How to customise Power BI matrix/pivot table (subtotal and grand total)

[MYDATASTORY]

Hi all,

I’m trying to calculate subtotals of the balance sheet in Power BI pivot table matrix to follow customized formulae which I am using get the subtotal on balance sheet, but power BI keep defaulting to default subtotal, please see dummy data provided and desired output, any example/steps provided will be highly appreciated.

 Summary:

Trying to subtract Power BI  Pivot/Matrix table with each subgroup using a different rule. 

Below are the formulae I would like to add to the  Power BI customised Pivot table/Matrix

Customised formulae required on matrix tablePivot table/Matrix I expect to get on right hand side and the left hand side the default Matrix which I am trying to customise but keep defaulting to summing every group

Thanks so much for your advice.

Raw dummy data,

Required format calculating Subtotals and Grand totals on Balance sheet

[parry2k]

@MYDATASTORY  You can achieve this with using new DAX function ISINSCOPE, here are more details, athough some work around required but it can be achieve.

[MYDATASTORY]

@parry2k

That's so much for your prompt response, still not clear, are you able to provide an example based on my dummy data formulas for these subtotals and grand total and how they would all appear at one column.

I have provided below image with dummy data and Expected results, but not clear how to get this from the Power BI matrix.

Thanks so much for your time and help.

Please see below Excel formulae expected to be converted to Dax

Expected output as a result of the above formulae 

Raw dummy data and expected results from Power BI Matrix.

[MYDATASTORY]

@Anonymous  Please if is possible help on how solved the similar issues you had on subtotal calculations any advice will be highly appreciated.

[v-yulgu-msft]

Hi @MYDATASTORY ,

There is no direct way to achieve the same output in Power BI desktop. As a workaround, you could create a higher level group to class main groups. Use grouping and binning in Power BI Desktop

Best regards,

Yuliana Gu

[MYDATASTORY]

Hi @Yuliana Gu

Much appreciated for your guidance, any chance you able to provide pbix file with these DAX formulae you have calculated as shown on the screenshot.

Many thanks, in advance

[v-yulgu-msft]

Hi @MYDATASTORY ,

I have uploaded the .pbix file for your reference.

Regards,

Yuliana Gu