Share of total% with ISINSCOPE (SOLVED/DONE)

DAX DAX Calculations
1

Hi,
I watched Brians video some time ago with how to correctly calculate the share of total% using ISINSCOPE.

In my example i have “competitors” and “articles” so I would like to calculate the share of “competitors” by the grand total and then the “articles” within the competitors so they are the grand total.

GrandTotal_IsInScope.pbix (18.7 KB)

So when using this measure the DAX looks like this:

Sales share% = 
VAR AllSales =
    CALCULATE ( [Sales], REMOVEFILTERS ( PricesAndProducts ) )

VAR AllCompetitor =
    CALCULATE ( [Sales], REMOVEFILTERS ( PricesAndProducts[Competitor] ) )

VAR Result =
    SWITCH (
        TRUE (),
        ISINSCOPE ( PricesAndProducts[Competitor] ), DIVIDE ( [Sales], AllCompetitor ),
        DIVIDE ( [Sales], AllSales )
    )
RETURN
    Result

Buts its returning the wrong result. I have attached two print screens with what the DAX measure produces a result with no filter and then with filtering on “Ebay DE” with the correct result.

WRONG result - i.e. the 10.7% should be 15.7% as in the print screen below:
image

CORRECT result but im using a filter on Ebay DE only which i do not want to do:
image

2

Hi,

is it possible for you to share the pbix so i try to solve your query.

Thanks,
Anurag

4

Hello @Tibbie,

Thank You for posting your query onto the Forum.

To achieve the results, below is the measure alongwith the screenshot of the final results provided for the reference -

Sales Share % - Harsh = 
VAR _Condition_1 = 
SUM( PricesAndProducts[Sales] )

VAR _Condition_2 =
CALCULATE( SUM( PricesAndProducts[Sales] ) , 
    ALLSELECTED( PricesAndProducts[ArticleID] ) )

VAR _Condition_3 =
CALCULATE( SUM( PricesAndProducts[Sales] ) , 
    ALLSELECTED( PricesAndProducts[Competitor] ) )

VAR _Results = 
SWITCH( TRUE() , 
    ISINSCOPE( PricesAndProducts[ArticleID] ) , 
        DIVIDE( _Condition_1 , _Condition_2 , 0 ) , 
    ISINSCOPE(PricesAndProducts[Competitor] ) , 
        DIVIDE( _Condition_2 , _Condition_3 , 0 ) ,
        DIVIDE( _Condition_1 , _Condition_2 , 0 ) )

RETURN
_Results

Final Results
Final Results575×738 28.9 KB

I’m also attaching the working of the PBIX file for the reference purposes.

Hoping you find this useful and meets your requirements that you’ve been looking for. :slightly_smiling_face:

Thanks and Warm Regards,
Harsh

GrandTotal_IsInScope - Harsh.pbix (18.8 KB)

1 Like