Matrix subtotal with no data

[thomma]

Hi all,

I have a matrix with subrows calculating row total summing the subrows, see image below. Problem is, I don't have the subrow data for all periods, but I do know the row total value for all columns which are calculated in an other column/table. What I want is to use the column containing the subtotal row when sub rows are empty and summing the sub rows to calculate the subtotal when the data is available. Summing when I have the subrow data is not a requirement as I also have the data in another column, but to me it seems to be the only possibilty to make this work when adding the sub rows. Does anyone know a good solution for this? 

Thanks in advance!

[amitchandak · Accepted solution]

@thomma , using isinscope and value of the row, we can replace the measure with another measure example

If(isinscope(Table[Level 2])  && isblank([M1]) , [M2], [M1] ) 

insinscope should be added from lowest to highest level in if and switch

Replace with value

If(isinscope(Table[Level 2])  && Max(Table[Level 2]) ="ABC" , [M2], [M1] ) 

Force some levels

If(isinscope(Table[Level 2])  && Max(Table[Level 2]) ="ABC" , calculate( [M2], [Level1] in {"1","2"} ), [M1] ) 

Power BI How to get the P&L formatting right: https://youtu.be/C9K8uVfthUU
Power BI How to get two columns format Profit and Loss Statement(P&L) right: https://youtu.be/WLg85yiMgHI
https://medium.com/microsoft-power-bi/power-bi-formatted-p-l-with-custom-sub-totals-and-blank-rows-e...