Different DAX calculations for different row hierarchies in a matrix table? Independent subtotals?

[Grygger]

I have a matrix visual in which I am trying to show sales targets for each sales territory. These territories then roll up into a region. The challenge is that sales regions have higher sales targets than all their respective territories and therefore I cannot aggregate the territory sales targets to get the region's overall sales target (regional sales targets being higher).

In a PowerBI matrix, I'm trying to create a DAX formula that would populate the respective region's target and each respective territories target based on the row being shown. Keep in mind, these are independent of eachother.

I have this formula here which is not working. What I'm intending to do is if the row contains a region, then return a calculation that aggregates the sales targets for that specific region. Otherwise, it'll return sales territory targets.

The 'role' column defines whether the target is specific to a region or to a territory, having values as either Region or Territory.

Target Switch Function = 
VAR CurrentRegion = SELECTEDVALUE(Revenue[Territory])

RETURN 
SWITCH(TRUE(), 
    CurrentRegion = "AM-CA-CA-PR", CALCULATE(SUM(Revenue[Target]), Revenue[Role]="Region"),
    CurrentRegion = "AM-CA-CA-PS", CALCULATE(SUM(Revenue[Target]), Revenue[Role]="Region"),
    CALCULATE(SUM(Revenue[Target]), Revenue[Role]="Territory"))

My existing matrix table looks like

And my desired output is below, with the regional subtotals being higher than the territorial. Note that the region totals are higher than the total sum of the total territories and this is what I'm trying to get the DAX formula to do.

[v-angzheng-msft · Accepted solution]

Hi, @Grygger 

Using the IF and ISINSCOPE functions, you can customize the calculation of each level of the matrix.
I'll use an example to illustrate this, see the following screenshot.

Switch = 
var _territoty=1
var _region=2
var _Total=3
return 
IF (
    ISINSCOPE ( 'Table'[Region] ),
    IF ( ISINSCOPE ( 'Table'[Territory] ), _territoty, _region ),
    _Total
)

Please refer to the attachment below for details. Hope this helps.

Best Regards,
Community Support Team _ Zeon Zheng

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[kleytonrbf]

And for it?

1

2

3 = 1 - 2

4

5 = 3 - 4

6

7 = 5 + 6

This table is a "table/matrix".

The calcuus is different for each row or row 3, 5 and 7. Can you help me?

[YukiK]

You may be able to leverage a function like ISINSCOPE() to check if a specific column is in scope. I'd try something like:

IF ( ISINSCOPE( REGION ), Measure1, Measure2 )

Hope that helps!