topic Calculating percentage total and grand total for Matrix table in DAX Commands and Tips

Calculating percentage total and grand total for Matrix table

Marshmallow — Mon, 27 Nov 2023 02:26:05 GMT

I am trying to build a matrix table that will show the value as well as the percentage. I have included screenshot below. I am stuck with the grand total percentage as it now shows the grand total of each row instead of 100%.

This is the data source:

I am building the table to show it as is in the matrix format but i am unable to get the column D (state share portion) right. It either will show Grand total as 500% or it will calculate percentage by row to make the grand total of 100%.

Can someone please help?

Re: Calculating percentage total and grand total for Matrix table

Jihwan_Kim — Mon, 27 Nov 2023 05:18:16 GMT

Hi,

I am not sure how your semantic model looks like but I tried to create a sample pbix file like below.

Please check the below picture and the attached pbix file.

State share percentage: = SWITCH ( TRUE (), ISINSCOPE ( 'Product'[Product] ), DIVIDE ( [Total Quantity:], CALCULATE ( [Total Quantity:], ALL ( 'Product'[Product] ) ) ), ISINSCOPE ( State[State] ), DIVIDE ( [Total Quantity:], CALCULATE ( [Total Quantity:], ALL ( 'Product'[Product] ), ALL ( State[State] ) ) ), DIVIDE ( [Total Quantity:], CALCULATE ( [Total Quantity:], ALL ( 'Product'[Product] ), ALL ( State[State] ) ) ) )

Re: Calculating percentage total and grand total for Matrix table

Marshmallow — Mon, 27 Nov 2023 05:37:17 GMT

Thank you, it works! 🙂