Different calculations for Row Subtotal and for Column Subtotal

[Anonymous]

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!

[amitchandak · Accepted solution]

@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/

[v-robertq-msft · Accepted solution]

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.

[amitchandak · Accepted solution]

@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/

[Anonymous]

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.