Power BI Exchange

Please login or click SIGN UP FOR FREE to create your PowerBIUG account to join this user group.
Expand all | Collapse all

DAX Logic Help - Average of Sum

  • 1.  DAX Logic Help - Average of Sum

    Top Contributor
    Posted Jul 31, 2019 10:17 AM

    Hey all: 

    It baffles me that the following is as complicated as it is - I would think the answer should be simple.

    I have a fact table, and I need to know the average total of each 'KEY'.

    So, logically, I need to first find the sum of each key, and then average the 'Sum of Keys'. Maybe I am over thinking this, but the answer seems to be more complicated than it should be.

    looking for any 'logic' recommendations.

    Thanks!
    William
    #DAX



    ------------------------------
    William Rodriguez
    Business Analyst | MCSA: BI Reporting
    ------------------------------
    Conference-PBI_200x200


  • 2.  RE: DAX Logic Help - Average of Sum

    Posted Jul 31, 2019 12:05 PM
    William,

    Using these example values I think I got it to work. See if this measure works for you.

    AVG = AVERAGEX(SUMMARIZE(Table1,Table1[Key], "Average By Key",SUM(Table1[AMT])),[Average By Key])





    ------------------------------
    Mara Kinoff
    Run Business Solutions
    Amarillo TX
    806-322-2151
    ------------------------------

    Conference-PBI_200x200


  • 3.  RE: DAX Logic Help - Average of Sum

    Top Contributor
    Posted Jul 31, 2019 12:37 PM
    Edited by William Rodriguez Jul 31, 2019 12:38 PM

    Hi @Mara Kinoff:

    I ​like your solution - it's more dense than mine; same logic though.

    I guess this is the only way to accomplish the business requirement. Even different variants such as GROUPBY work off the same logic.

    Thanks though,
    William


    AVERAGEX(
    	GENERATE(
    		SUMMARIZE(
    			Table, 
    			Table[Key]
    		) , 
    		ROW (
    			"__AMT" , 
    				VAR __K= [Key]
    				RETURN 
    				SUMX(
    					FILTER(
    						Table , 
    						Table[Key] = __K
    					) , Table[AMT]
    				)
    		)
    	) , [__AMT]
    )


    ------------------------------
    William Rodriguez
    Business Analyst | MCSA: BI Reporting
    ------------------------------

    Conference-PBI_200x200