Forum Discussion
Matrix subtotal with no data
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!
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-e39ca822908a
1 Reply
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-e39ca822908a