Calculating percentage total and grand total for Matrix table

[Marshmallow]

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?  

[Marshmallow]

Thank you, it works! 🙂

[Jihwan_Kim · Accepted solution]

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] )
        )
    )
)