Forum Discussion
Different calculations for Row Subtotal and for Column Subtotal
Hello Team,
I have been many hours trying to solve this issue without success.
How could I get a specific calculation for the row subtotal (instead of the sum by default), and another differnt calculation for the column subtotal?
I managed to get the average for row subtotals with AVERAGEX(VALUES....) but once I enable subtotals per column by default the operation SUM is applied.
Thanks for your support!
Anonymous , isinscope can help to switch the calculation.
In this case, you need to switch the column using AVERAGEX(VALUES....)
if(isinscope([Row]) && not(isinscope([Column])), AVERAGEX(VALUES([column]), [measure]), [Measure])
Use Switch to handle condition for both row and column
https://www.kasperonbi.com/use-isinscope-to-get-the-right-hierarchy-level-in-dax/
Hi, Anonymous
According to your description and sample picture, I think you can try to create a measure to replace the original measure [Selector Measure Summary] in the matrix:
Measure 2= IF ( ISINSCOPE ( 'Table'[Level1] ) && NOT ( ISINSCOPE ( 'Table'[Level2] ) ), AVERAGEX(Table,[Selector Measure Summary]), [Selector Measure Summary] )Here’s a case that I dealt with that has a similar requirement before, you can also take a look:
https://community.powerbi.com/t5/Desktop/Matrix-Filtering-by-Rolled-up-Top-Level-Value/td-p/1836365
If you still have a problem, you can post some sample data(without sensitive data) and your expected result.
How to Get Your Question Answered Quickly
Thank you very much!
Best Regards,
Community Support Team _Robert Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
- amitchandak
Super User
Anonymous , isinscope can help to switch the calculation.
In this case, you need to switch the column using AVERAGEX(VALUES....)
if(isinscope([Row]) && not(isinscope([Column])), AVERAGEX(VALUES([column]), [measure]), [Measure])
Use Switch to handle condition for both row and column
https://www.kasperonbi.com/use-isinscope-to-get-the-right-hierarchy-level-in-dax/
Thanks for prompt response. Let me share with you the actual status of my matrix so I can understand better how to impelment isinscope:
I have a selector A B C and I have the AVERAGEX of the items selected at that moment in Row Subtotal. Then I have Subtotal Column that is sight now adding up all the elements but I would like to have the average of the items selected as well.
As you can see I have dates in the column headers and then the subtotal which should be the average of the values during that 14 days.
I do not see clearly how to implement isinscope. If you could tell me some tips I'd appreciate it.
- v-robertq-msft
Community Support
Hi, Anonymous
According to your description and sample picture, I think you can try to create a measure to replace the original measure [Selector Measure Summary] in the matrix:
Measure 2= IF ( ISINSCOPE ( 'Table'[Level1] ) && NOT ( ISINSCOPE ( 'Table'[Level2] ) ), AVERAGEX(Table,[Selector Measure Summary]), [Selector Measure Summary] )Here’s a case that I dealt with that has a similar requirement before, you can also take a look:
https://community.powerbi.com/t5/Desktop/Matrix-Filtering-by-Rolled-up-Top-Level-Value/td-p/1836365
If you still have a problem, you can post some sample data(without sensitive data) and your expected result.
How to Get Your Question Answered Quickly
Thank you very much!
Best Regards,
Community Support Team _Robert Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.