topic Re: Calculate the percentage of table matrix as well as subtotals in DAX Commands and Tips

Calculate the percentage of table matrix as well as subtotals

Anonymous — Thu, 03 Mar 2022 09:08:55 GMT

I need to calculate the Share of the below table, I was able to calculate in Pbi the share per Brand by using this DAX
=DIVIDE([Sales_], CALCULATE([Sales_], ALLEXCEPT('tblProduct','tblProduct'[Brand_name])))

However subtotal needs to calculate the share base on column total (sub-total / Column Total)

In my case sub-total is returning 100%

Requirements: i write here as well the excel formula (Column F) on how to calculate the share and its sub-total.

Re: Calculate the percentage of table matrix as well as subtotals

amitchandak — Thu, 03 Mar 2022 09:30:34 GMT

Anonymous , Create a measure like

if(isinscope('tblProduct'[Model]) ,

DIVIDE([Sales_], CALCULATE([Sales_], ALLEXCEPT('tblProduct','tblProduct'[Brand_name]))),

DIVIDE([Sales_], CALCULATE([Sales_], ALL()))

)

Re: Calculate the percentage of table matrix as well as subtotals

tamerj1 — Thu, 03 Mar 2022 09:40:28 GMT

Hi Anonymous
You can use

Share per Brand = VAR ProductSahre = DIVIDE ( [Sales_], CALCULATE ( [Sales_], ALLEXCEPT ( 'tblProduct','tblProduct'[Brand_name] ) ) ) VAR BrandShare = DIVIDE ( [Sales_], CALCULATE ( [Sales_], REMOVEFILTERS ( 'tblProduct' ) ) ) VAR Result = IF ( HASONEVALUE ( 'tblProduct'[Model] ), ProductSahre, BrandShare ) RETURN Result

Re: Calculate the percentage of table matrix as well as subtotals

Anonymous — Thu, 03 Mar 2022 09:51:03 GMT

It works! 🙂

I have another requirements, same table as my example but we need to add additional column at Highest level, but this comes from another table,
lets call the column as Customer_name from tblCustomer.

Re: Calculate the percentage of table matrix as well as subtotals

Anonymous — Thu, 03 Mar 2022 11:06:29 GMT

This works as well, together with using dims in another table, thanks a lot!