Forum Discussion
Working with tidy/long data Grand Total Incorrect When working with a GroupBy in a Variable.
- 6 years ago
You can change the code to this:
Total Costs = IF ( HASONEVALUE ( 'Attribute Slicer'[Attribute] ); SUM ( 'Attribute Slicer'[Detail_NET_AMOUNT_AMOUNT] ); CALCULATE ( SUM ( 'Attribute Slicer'[Detail_NET_AMOUNT_AMOUNT] ); FILTER ( 'Attribute Slicer'; 'Attribute Slicer'[Attribute] = SELECTEDVALUE ( 'Attribute Slicer'[Attribute] ) ) ) ) - 6 years ago
oops, the [Total cost]-measure was a bit more complicated than necessary. It should be written like this:
Total Costs = CALCULATE ( SUM ( 'Attribute Slicer'[Detail_NET_AMOUNT_AMOUNT] ); FILTER ( 'Attribute Slicer'; 'Attribute Slicer'[Attribute] = SELECTEDVALUE('Attribute Slicer'[Attribute]) ) )If you want the average across an attribute you could use this code:
Average Total Costs = AVERAGEX ( CALCULATETABLE ( DISTINCT ( 'Attribute Slicer'[value] ); ALLEXCEPT ( 'Attribute Slicer'; 'Attribute Slicer'[Attribute] ) ); CALCULATE( SUM ( 'Attribute Slicer'[Detail_NET_AMOUNT_AMOUNT] ) ) )But this will depend a bit on how you want to use it. Also think about how to handle blank values; should blank values count as 0 in the average, or should the average just be over the attribute values which have a value
- 6 years ago
It all depends on what you want to do. As you can see in your screenshot, [Average rate] is changing with each 'Attribute'[Value]. This is because your code now calculates the average rate for each 'Attribute'[Value]. E.g. look at the second line of the table Draft/revise. On this line the filter context is 'Attribute'[Value]="Draft/revise". This means that in this line, with your average calculation, you are calculating the average of rows from your table where 'Attribute'[Value]="Draft/revise". If this is you desired output, your code is perfect.
If you want to compare rates from, say company, to the average of all companies, you will have to use DAX-functions to alter the filter context, like the example code I sent you.
Cheers,
Sturla
It all depends on what you want to do. As you can see in your screenshot, [Average rate] is changing with each 'Attribute'[Value]. This is because your code now calculates the average rate for each 'Attribute'[Value]. E.g. look at the second line of the table Draft/revise. On this line the filter context is 'Attribute'[Value]="Draft/revise". This means that in this line, with your average calculation, you are calculating the average of rows from your table where 'Attribute'[Value]="Draft/revise". If this is you desired output, your code is perfect.
If you want to compare rates from, say company, to the average of all companies, you will have to use DAX-functions to alter the filter context, like the example code I sent you.
Cheers,
Sturla
sturlaws I appreciate it. This has been most helpful. Really really appreciate it.