Price index dashboard - wrong subtotal

Tibbie · October 13, 2021, 2:18pm

Hi,
I am struggling with some logic when it comes to the DAX calculations. It works easy in excel, but not when im transferring to PBI since I dont know how to replicate the same logic.

I have tried to explain in in the PBI file in a separate sheet with text and screen shots (as the picture below) PricingDashboard.pbix (784.3 KB)

But what I want to achieve is to have a “sub total” sum for one column but a “sub total” average on another column to then calculate unweighted and weighted price index between “our price” and competitor price.

So i have a measure called “price index unweighted” which is
DIVIDE( [Our price], [competitor price] ) * 100
This works on a row level but not on a column sub total level

and then i have a measure called “price index weighted” which is
[Price index unweighted] * [Share of sales%]
This also seem to work on a row level (i believe), but not on a column sub total level

Please ask questions if something is unclear in the attached file.

Btw, thanks @Harsh for help with the “share of total%” calculation in the other thread

Harsh · October 14, 2021, 5:14am

Hello @Tibbie,

Thank You for posting your query onto the Forum.

And you’re welcome. I’m glad that I was able to assist you on your previous query.

Now, coming back to this one. Let’s break this solution into two parts -

Part 1: Getting Correct Sub-Totals of Price Index Unweighted (1) - Harsh

This is one quite straight-forward. The reason why you got incorrect totals at the “Competitor” level is because you simply applied the measure at an entire level rather than only applying or considering it at an “ARTICLE_ID” level only.

So what actually happened here was it evaluated the “Average” results at all the levels rather than first summing it up at the “Competitor” and “Market Name” level and then dividing it by the number of rows of “ARTICLE_ID”.

To achieve the “Average” results at an individual level of “ARTICLE_ID” and to “Sum” them up at a “Market Name” and “Competitor” level and then divide them by the number of rows of “ARTICLE_ID”. Below is the measure alongwith the screenshot of the results provided for the reference -

Price Index Unweighted (1) - Harsh = 
VAR _Condition_1 = 
DIVIDE( 
    AVERAGE( PricesAndProducts[Our Price] ) , 
    AVERAGE( PricesAndProducts[Competitor Price] ) , 
    0 ) * 100

VAR _Condition_2 = 
DIVIDE(
    SUMX(
        ADDCOLUMNS(
            SUMMARIZE(
                    PricesAndProducts , 
                    PricesAndProducts[MarketName] , 
                    PricesAndProducts[Competitor] , 
                    PricesAndProducts[ARTICLE_ID] ,
                    "@Totals" , 
                    DIVIDE( 
                        AVERAGE( PricesAndProducts[Our Price] ) , 
                        AVERAGE( PricesAndProducts[Competitor Price] ) , 
                        0 ) * 100 ) ,
                "@Totals - Harsh" ,
                IF( [@Totals] > 0 , [@Totals] , BLANK() ) ) ,
        [@Totals - Harsh] ) ,
    COUNTROWS( VALUES( PricesAndProducts[ARTICLE_ID] ) ) ,
    0 )

VAR _Results = 
SWITCH( TRUE() , 
    ISINSCOPE( PricesAndProducts[Competitor] ) , _Condition_2 , 
    _Condition_1 )

RETURN
_Results

So now, you’ll observe that the results which you were looking for “90.6” is correctly getting reflected. Part One ends here.

Part 2: Getting Correct Sub-Totals of Price Index Weighted (1) - Harsh

Now, this one was tricky. The reason being is, in the previous post, you considered your condition was like this -

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.

And that’s what actually was happening here in your second case. So when you used to calculate the proportion at an individual levels i.e. at an “ARTICLE_ID” level, it also used to calculate the proportions at a “Market Name” and “Competitor” level.

For example, the percentage or sales share of “Competitor No. 3” is “5.4%” and based on this figure, it used to calculate the figures accordingly.

Ideally, since you want to sum it up at a “Market Name” and “Competitor” levels that figure should have been “100%” i.e. consolidation of all the % sales share of “ARTICLE_ID” at their respective “Competitor” levels. And thereby, consolidation of each “Competitor” level at their “Market Name” level. And that’s how, you’ll actually be able to achieve the results. Below is the measure alongwith the screenshot of the results provided for the reference -

Price Index Weighted (1) - Harsh = 
VAR _Condition_1 =
DIVIDE( 
    AVERAGE( PricesAndProducts[Our Price] ) , 
    AVERAGE( PricesAndProducts[Competitor Price] ) ,
    0 ) * 100 * 
DIVIDE( 
SUM( PricesAndProducts[Sales] ) , 
CALCULATE ( SUM( PricesAndProducts[Sales] ) ,
    ALLSELECTED( PricesAndProducts[ARTICLE_ID] ) ) ,
    0 )

VAR _Condition_2 = 
SUMX(
    ADDCOLUMNS(
        SUMMARIZE(
                PricesAndProducts , 
                PricesAndProducts[MarketName] ,
                PricesAndProducts[Competitor] , 
                PricesAndProducts[ARTICLE_ID] ,
                "@Totals" , 
                DIVIDE( 
                    AVERAGE( PricesAndProducts[Our Price] ) , 
                    AVERAGE( PricesAndProducts[Competitor Price] ) ,
                    0 ) * 100 * 
                DIVIDE( 
                SUM( PricesAndProducts[Sales] ) , 
                CALCULATE ( SUM( PricesAndProducts[Sales] ) ,
                    ALLSELECTED( PricesAndProducts[ARTICLE_ID] ) ) ,
                    0 ) ) ,
            "@Totals - Harsh" ,
            IF( [@Totals] > 0 , [@Totals] , BLANK() ) ) ,
    [@Totals - Harsh] )

VAR _Results =
SWITCH( TRUE() ,
    ISINSCOPE( PricesAndProducts[ARTICLE_ID] ) , _Condition_1 , 
    ISINSCOPE( PricesAndProducts[MarketName] ) || ISINSCOPE( PricesAndProducts[Competitor] ) , _Condition_2 ,
    _Condition_2 )

RETURN
_Results

Now, after observing the above screenshot, you must be wondering that - “Harsh, answer should have been “89.9” and not “89.8”.”

So does that mean, is my measure incorrect? - Absolutely, not. I just an decimal issue.

Once you round the figures upto 2 decimal places rather than 1, you’ll observe that numbers will start to get tallying out (Note: There’s nothing that can be done to solve this type of decimal issue). Below is the screenshot of the final results provided for the reference -

After the lengthy explanation, I guess you’ll also require a PBIX file so I’m 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.

Thanks and Warm Regards,
Harsh

PricingDashboard - Harsh.pbix (782.6 KB)

Tibbie · October 14, 2021, 6:21am

@Harsh Wow, your way of explaining is really good. I understand now, but could not achieve the result myself. Its so cool to see what PBI can do if you know how to “tame it”

What kind of DAX-topic should I improve on in this case to come to the conclusion that the solution should be done in this way?

This is exactly what I wanted to achieve so thank you very much for the time and effort!

Harsh · October 14, 2021, 6:33am

Hello @Tibbie,

You’re Welcome.

I’m glad that I was able to assist you in your query and you found the explanation useful.

So, in this scenario, if you break or analyze the measure into small steps then you’ll observe that several concepts came into the picture and they were collaborated together to achieve the final results.

So if you’ve gone through the “Mastering DAX Calculations” course, it’ll further strengthen your understanding of individual DAX functions and then you can integrate them to achieve the results like this.

Also, to fix the totals, one of our expert @Greg had already created a post where he has showcased various scenarios about how to fix them but this type of scenario was witnessed for the first time onto our forum. For understanding purposes, you can go through that post which you’ll find really helpful for future references. Below is the link of that post provided for reference.

Hoping you’ll find this helpful.

Note: In order to view the entire post, please click onto the link and not onto the “expand/collapse” button.

Thanks and Warm Regards,
Harsh