Controlling Row Subtotals both for expanded table and on any drill up and drilldown level

[smpa01]

My sample source data is following (pbix attached)

|                        key                                  |
|------|-------|----------|-------|-----------------------|---|
| Year | site  | service  | month | key                   |   |
| 2020 | site1 | service1 | 1     | site1-service1-2020-1 |   |
| 2020 | site1 | service1 | 2     | site1-service1-2020-2 |   |
| 2020 | site1 | service2 | 1     | site1-service2-2020-1 |   |
| 2020 | site1 | service2 | 2     | site1-service2-2020-2 |   |
| 2021 | site2 | service1 | 1     | site2-service1-2021-1 |   |
| 2021 | site2 | service1 | 2     | site2-service1-2021-2 |   |
| 2021 | site2 | service2 | 1     | site2-service2-2021-1 |   |
| 2021 | site2 | service2 | 2     | site2-service2-2021-2 |   |

|      fact           |
|-------|-------------|
| site  | closingYear |
| site1 | 2013        |
| site2 | 2014        |

|                                 transaction                            |
|-------------|-------|----------|-------|-----------------------|-------|
| Year        | site  | service  | month | key                   | value |
| 2020        | site1 | service1 | 1     | site1-service1-2020-1 | 100   |
| 2020        | site1 | service1 | 2     | site1-service1-2020-2 | 200   |
| 2020        | site1 | service2 | 1     | site1-service2-2020-1 | 300   |
| 2020        | site1 | service2 | 2     | site1-service2-2020-2 | 400   |
| 2021        | site2 | service1 | 1     | site2-service1-2021-1 | 500   |
| 2021        | site2 | service1 | 2     | site2-service1-2021-2 | 600   |
| 2021        | site2 | service2 | 1     | site2-service2-2021-1 | 700   |
| 2021        | site2 | service2 | 2     | site2-service2-2021-2 | 800   |

The schema is following.

I am building a viz where I am bringing service, site, month from key and want to show what is the sum of transaction[value] and what is the fact[closingYear].

My measures are following 

_closingYear = 
VAR _0 =
    CALCULATE (
        MAX ( 'fact'[closingYear] ),
        TREATAS ( VALUES ( 'key'[site] ), 'fact'[site] )
    )
RETURN
    _0

transaction = CALCULATE(SUM('transaction'[value]))

and when i drop the measures on the viz, the expanded table looks like this

At this stage, how can I ask DAX to not to generate any _closingYear value on any of the expanded table as well as for drill down level

E.g. I don't want  any values for highlighted (i.e. row subbtotal on the expanded table, row subtotal for drill up and drill down level)

expanded table

drilled up

drilldown

https://drive.google.com/file/d/12QeYfgUGCDKIFEzBh7tQ74bvYd9AtFea/view?usp=sharing

Thank you in advance.

[Anonymous · Accepted solution]

Hi @smpa01 

After my test, the total and row subtotals can only be removed from the specified column of a certain level, but it is not applicable to drill up and drill down. Unless you turn off the row subtotals in the format settings.

Best Regards

Community Support Team _ Ailsa Tao

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

[smpa01]

I can create following measures to determine if is in scope

isSiteinScope = if(ISINSCOPE('key'[site]),"y","n")

isServiceinScope = if(ISINSCOPE('key'[service]),"y","n")

isMonthinScope = if(ISINSCOPE('key'[month]),"y","n")

and then, create following measures

_closingYearRevised1 = SWITCH(TRUE(),
                                     [isSiteinScope]="y"&&[isServiceinScope]="y"&&[isMonthinScope]="y",[_closingYear]
                         
) 

_closingYearRevised2 = 
VAR _0 = SWITCH(TRUE(),
                               [isMonthinScope]="n"&&[isServiceinScope]="y"&&[isSiteinScope]="y",[_closingYear]
                                   
)
RETURN _0

_closingYearRevised3 = SWITCH(TRUE(),
                                     [isMonthinScope]="n"&&[isServiceinScope]="n"&&[isSiteinScope]="y",[_closingYear]
                                   
) 

But how can I make them mutually exclusive (not all the above measures [_closingYearRevised] can evaluate at the same time)?