Regarding Dimensional Table

[Muthuramalingam]

We are using the variance chart to show the profit and loss statement on year on year basis with a financial chart of accounts. The dimension table is made in an excel sheet. We have used two hierarchies namely the first hierarchy represents the total amount, and the second hierarchy represents the line items (lower node). To calculate gross profit (Net sales-cost of sales), we have to add again Net sales and cost of sales line items in gross profit in the dimension table.
While using this dimensional table in the variance chart the entire line items of net sales and cost of sales again appear in gross profit. However, we need to show the Gross profit as a "subtotal" being the result of the value of net sales and cost of sales, and don't need to show the line items below the Gross profit.

I would like to show subtotals(KPI) of profit and loss statements such as gross profit, operating income, etc in the default matrix chart

The dimensional table is made in the excel sheet when I try to add the dim table the entire line item of the p&l including the KPIs is shown, without any option to add subtotals
I have attached the sample pbix file and dimensional table. if anyone can help me with how to show subtotals in the default matrix chart it would be helpful

https://docs.google.com/spreadsheets/d/1A3da7TVDD5usw41S8fsWfnpj2EaxWg3R/edit#gid=349591095 - sample excel file

[Anonymous]

Hi @Muthuramalingam ,

Could you tell me if your problem has been solved? If it is, kindly Accept it as the solution. More people will benefit from it.

Or if you are still confused about it, please provide me with more details about your table and your problem or share me with your pbix file after removing sensitive data.

Best Regards,
Eyelyn Qin

[Muthuramalingam]

Hi 

The issue is not yet solved. We tried the solutions but we didn't meet our requirements. I will share my pbix file 

[Anonymous]

Hi @Muthuramalingam ,

If you want to change the row sub-total in matrix, you could try ISINSCOPE():

Measure = IF(ISINSCOPE('Table'[Sub-type]),SUM('Table'[Value]),0)

Then you could replace 0 with your wanted measure.

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