topic Re: Total of Total Column in DAX Commands and Tips

Total of Total Column

Euto — Wed, 19 Aug 2020 17:33:57 GMT

This is a sample data

This is what I am getting = $1,300 with "AVERAGE(DanSampleData[Rent])"

This is what I am expecting = $1,291

Please how do I contruct my dax ?

I have tried the following avgrent1 to avgrent6 with no luck.

MyTotal =

// Varibles

var Countofrows = CALCULATE(DISTINCTCOUNT(DanSampleData[Building]), ALL(DanSampleData))

var Agvrent = CALCULATE(AVERAGE(DanSampleData[Rent]), ALL(DanSampleData))

// Attempts

var avgrent1 = AVERAGE(DanSampleData[Rent])

var avgrent2 =

IF(HASONEVALUE(DanSampleData[Building]),

avgrent1,

SUMX(VALUES(DanSampleData[Building]), avgrent1)

)

var avgrent3 = CALCULATE(DIVIDE(Agvrent, Countofrows, 0), ALL(DanSampleData[Building]))

var avgrent4 =

CALCULATE(

AVERAGE(DanSampleData[Rent]),

ALLEXCEPT(DanSampleData, DanSampleData[Building], DanSampleData[Units])

)

var avgrent5 = AVERAGEX(DanSampleData, DanSampleData[Rent])

var avgrent6 = AVERAGEX(VALUES(DanSampleData[Building]), AVERAGE(DanSampleData[Rent]))

RETURN

avgrent6

Re: Total of Total Column

AlejandroAldama — Wed, 19 Aug 2020 17:10:22 GMT

Instead of AVERAGE try using AVERAGEX

Re: Total of Total Column

amitchandak — Wed, 19 Aug 2020 17:14:04 GMT

Euto , Try like

AVERAGEX(values(Table[Building]),AVERAGE(DanSampleData[Rent]))

Re: Total of Total Column

Euto — Wed, 19 Aug 2020 17:55:33 GMT

Thanks, but it didn't work.

Re: Total of Total Column

Anonymous — Wed, 19 Aug 2020 18:53:19 GMT

Hi Euto ,

Try this measure

avg rent = SWITCH ( TRUE (), NOT ( ISINSCOPE ( 'Table'[Building] ) ) && NOT ( ISINSCOPE ( 'Table'[Unit] ) ), SUMX ( VALUES ( 'Table'[Building] ), DIVIDE ( CALCULATE ( AVERAGE ( 'Table'[Price] ) ), DISTINCTCOUNT ( 'Table'[Building] ) ) ), ISINSCOPE ( 'Table'[Unit] ), AVERAGE ( 'Table'[Price] ), ISINSCOPE ( 'Table'[Building] ), AVERAGE ( 'Table'[Price] ) )

Regards,
Harsh Nathani
Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

Re: Total of Total Column

Euto — Wed, 19 Aug 2020 19:08:36 GMT

Anonymous this worked like magic! Thank you!

I sasid "magic" lol because I am not able to understand what this measure is doing.

Can this be simplified? or further explained ?. I would appriciate. Thanks

Re: Total of Total Column

Anonymous — Wed, 19 Aug 2020 19:13:48 GMT

Hi Euto ,

Understanding of ISINSCOPE is the crux here.

https://www.youtube.com/watch?v=sSUCyps_1O4

Regards,
Harsh Nathani
Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

Re: Total of Total Column

Euto — Wed, 19 Aug 2020 20:47:12 GMT

Anonymous I am sorry, I may have to uncheck your solution as the solution. I will provide the solution and explain further below.

Solution

MyTotal = var vTable = VALUES('DanSampleData'[Building]) var bldCount = DISTINCTCOUNT('DanSampleData'[Building]) RETURN SUMX (vTable, DIVIDE( CALCULATE( AVERAGE('DanSampleData'[Rent])), bldCount) )

IMAGE OF SOLUTION APPLIED

Explanation
Your solution above did give me the expected result, but the construct of the DAX was overkill. It would not realy help someone or someone else to use this solution.

I debugged the DAX you provided into several stages using variables.

Stage 1

MyTotal = var _Value1 = NOT (ISINSCOPE ( 'DanSampleData'[Building]))&& NOT (ISINSCOPE ( 'Table'[Unit] )) var _Result1 = SUMX (VALUES ( 'DanSampleData'[Building] ),DIVIDE (CALCULATE (AVERAGE ( 'DanSampleData'[Rent] )),DISTINCTCOUNT ( 'DanSampleData'[Building] ))) var _Value2 = ISINSCOPE ( 'DanSampleData'[Rent] ) var _Result2 = AVERAGE ( 'DanSampleData'[Rent] ) var _Value3 = ISINSCOPE ( 'DanSampleData'[Building] ) var _Result3 = AVERAGE ( 'DanSampleData'[Rent] ) var Final = SWITCH (TRUE () ,_Value1, _Result1 ,_Value2, _Result3 ,_Value3, _Result3 ) RETURN Final

In stage 1, with the Dax above, I realized that returning only the _Result1 give me the expected outcome, and I did not need to use the "ISINSCOPE" and "SWTITCH" funtions.

Which means the result is

MyTotal = SUMX (VALUES ( 'DanSampleData'[Building] ),DIVIDE (CALCULATE (AVERAGE ( 'DanSampleData'[Rent] )),DISTINCTCOUNT ( 'DanSampleData'[Building] )))

Stage 2

MyTotal = var vTable = VALUES('DanSampleData'[Building]) var bldCount = DISTINCTCOUNT('DanSampleData'[Building]) RETURN SUMX (vTable, DIVIDE( CALCULATE( AVERAGE('DanSampleData'[Rent])), bldCount) )

In Stage 2, with the dax above it's much easier to see what is been calculated. Even with this I still have some observations and questions

- Observation

1. Using "CALCULATE(AVERAGE('DanSampleData'[Rent]))" in my RETURN works, but using "AVERAGE('DanSampleData'[Rent])" did not work.

2. Using "CALCULATE(AVERAGE('DanSampleData'[Rent]))" in variable like "avgTable = CALCULATE(AVERAGE('DanSampleData'[Rent]))" and then calling the variable in my return does not work. Which is why i did not use variable here.

- Questions

1. What is the different between "CALCULATE(AVERAGE('DanSampleData'[Rent]))" and "AVERAGE('DanSampleData'[Rent])" ? note that no filter is been applied to the Calculate function
2. Why does it not work when is use "CALCULATE(AVERAGE('DanSampleData'[Rent]))" in variable like "avgTable = CALCULATE(AVERAGE('DanSampleData'[Rent]))" ?