Top 5 Product Sales II Distribution Mismatch Quarterly II Year 2020

Nisha · July 6, 2022, 9:17am

This is regarding a visual Top 5 Product Sales
Sam has written DAX for it but I am not writing DAX for this
I am doing “Filtering visual” by Top 5 products.
Now ,the issue comes that our Total Sales for the year 2020 is same but the distribution of Sales in Quarters are different .
So,I want to understand why it is so?

I am attaching both pbix file for me and Sam as well
Power BI Accelerator -Week 1 MyFile.pbix (8.6 MB)
Power BI Accelerator -Week 1 Problem.pbix (8.6 MB)

Top 5 Product Sales =
CALCULATE( [Total Sales],
TOPN( 5, Products, [Total Sales], DESC ) )

piniusz · July 6, 2022, 11:16am

Hello Nisha,

Investigating that was kinda surprising. That was a good question. The cause for this is a row context.
When you are using TopN filter, visual is not including your row context, that is quarter, and the products that is is selecting are the top products for entire year is summing up it’s sales for each quarter

And when using RankX measure, it is including row context, so it is taking top 5 products with highest sales in a given quarter, not entire year.

So it is better to use top 5 products sales

Keith · July 6, 2022, 11:38am

Hi @Nisha,
The visual was for top 5 products totals by quarter.

In reviewing your file you had the wrong measure in the visual.

instead of

Second part (filter pane had the wrong information in it)

instead of

I have attached your updated file for your review.

I made the same mistake when i did the problem months ago in the first Accelerator.

i hope this helps.
Keith
Power BI Accelerator -Week 1 MyFileNisha fixed by keith.pbix (8.6 MB)

Harsh · July 6, 2022, 1:15pm

Hello @Nisha,

Thank You for posting your query onto the Forum.

This is one of the few example about how Filter Pane works in a bizarre manner. Let’s break the situation step-by-step about why the two results are different but the totals are still same.

1). Results Based On Filter Pane -

In this case, when you tried “Top 5 Products by Sales” using Filter Pane, it evaluated the context externally first over all the products which are in Top 5 for the entire year. The reason why this happened is because, it’s a normal behaviour of Filter Pane since it only takes into account only one single context.

For example, remove the “Quarter & Year” from the visual and replace it with “Product Name”. Below is the screenshot provided for the reference -

So once, it evaluated the results for Top 5 Products externally, inside the table visual it just bifurcated the results based on quarters for the selected year. At the back-end, this is how the results are evaluated. Below is the screenshot provided for the reference -

So in this scenario, external context took precedence over the internal context.

2). Results Based On Top N Measure -

In this case, the context was defined inside the measure i.e., Total Sales based on Top 5 Products. And now, when it’s brought inside the table visual it’s first evaluated by the internal context i.e., which are the Top 5 products in each particular quarter and THEN for that particular selected year.

Again, for example, remove the “Quarter & Year” from the visual and replace it with “Product Name”. Below is the screenshot provided for the reference -

You’ll observe that it’s calculating the results for all the products and not for Top 5. But above I said, it’s evaluating it internally first. See how it evaluates the results at the back-end. Below is the screenshot provided for the reference -

And that’s how, it evaluates the results internally. So now, when you again replace the “Product Name” with “Quarter & Year”. It calculates the Top 5 Products for each “Quarter & Year” rather than calculating it through-out.

So in this scenario, internal context took precedence over the external context.

So finally, you must be wondering why Totals for both the tables are still same. The reason being is, for first scenario, it’s evaluated correctly but in the case of second instance, at an individual row level, it’s calculating Top 5 Products dynamically in a correct manner but what actually happens at a Total level is it again starts to evaluate the Top 5 Products for the entire year since there’s just no context available there. And that’s why, total is wrong. But one can easily fix it by writing the small simple measure -

Totals = 
SUMX(
    SUMMARIZE(
        Dates , 
        Dates[Quarter & Year] , 
        "@Totals" , 
        [Top 5 Products (Sales)] ) , 
    [@Totals] )

I’m also providing a link of the post in case if you want to understand in-depth about how Filter Pane works differently.

Hoping you find this explanation helpful and clears all your doubts.

Thanks and Warm Regards,
Harsh