Calculate the percentage of table matrix as well as subtotals

[Anonymous]

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.

[amitchandak · Accepted solution]

@Anonymous , Create a measure like

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

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

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

)

[Anonymous]

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

[tamerj1 · Accepted solution]

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

[amitchandak · Accepted solution]

@Anonymous , Create a measure like

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

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

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

)

[Anonymous]

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.