topic Re: TOP 3 measure not working in DAX Commands and Tips

TOP 3 measure not working

Rai_BI — Tue, 06 Jun 2023 12:07:20 GMT

Hello everyone, I need to clarify a doubt.

I need to make a measure following the two steps below:

1st Find out which are the ID codes of the products that are the TOP 3 biggest in sales of the company;

2nd Calculate sales by filtering these ID codes found in the previous step.

I want to do all the steps above in one single code. In my data model there are two related tables, a fact table called "Sales - 8027" and a dimension table called "Products - 8097".

I did tests on two practically identical measures, but only one works even though I don't attend to the two steps mentioned.

In the first measure had to create a physical table in my data model called "TOP 3" containing the product codes that are the TOP 3. This physical table was made with the following code:

TOP 3 =
VAR
Tab1 =
FILTER(
SUMMARIZE('Products - 8097','Products - 8097'[id],"@Rank",[Rank]),
[@Rank] <= 3
)
RETURN

SUMMARIZE(Tab1,'dProducts - 8097'[id])

Then I did the following:

Top 3 sku v1 =
CALCULATE(
SUM('Sales - 8027'[Total USD]),
FILTER('Products - 8097','Products - 8097'[id] IN VALUES('TOP 3'[id])))

It worked, after creating a physical table and then creating a measure, the result came out as I expected, but I wanted to create everything in a single measure to avoid creating many tables in the model, as mentioned in steps 1 and 2.

So I did it all in a single code as below:

TOP 3 SKU v2 =
VAR
Tab1 =
FILTER(
SUMMARIZE('Products - 8097','Products - 8097'[id],"@Rank",[Rank]),
[@Rank] <= 3
)
VAR Tab2 = SUMMARIZE(Tab1,'Products - 8097'[id])

RETURN

CALCULATE(
SUM('fSales - 8027'[Total USD]),
FILTER('Products - 8097','Products - 8097'[id] IN Tab2)
)

This measure did not work, instead of returning sales of only the TOP 3 products, it returns sales of all products. I don't understand why this happens. Why does the measure "Top 3 sku v1" work but "TOP 3 Sku v2" does not work? Can anyone explain?

If anyone has a different suggestion for achieving this goal I'd be happy to see it.

Re: TOP 3 measure not working

Anonymous — Thu, 08 Jun 2023 06:20:44 GMT

HI Rai_BI,

Measure expression should calculate based on the current row context. I haven't found any all function in your expression so they will be filtered to get result based current row category groups.

If current row context has aggregated less than three rows, it will be always displayed because the current dynamic rank has match the conditions.
For this scenario, I'd like to suggest you add ALLSELECTED function in these expression calculations and try again:

TOP 3 SKU v2 = VAR Tab1 = FILTER ( SUMMARIZE ( ALLSELECTED ( 'Products - 8097' ), 'Products - 8097'[id], "@Rank", [Rank] ), [@Rank] <= 3 ) RETURN CALCULATE ( SUM ( 'fSales - 8027'[Total USD] ), FILTER ( ALLSELECTED ( 'Products - 8097' ), 'Products - 8097'[id] IN SELECTCOLUMNS ( Tab1, "ID", [id] ) ) )

Regards,

Xiaoxin Sheng

Re: TOP 3 measure not working

Rai_BI — Mon, 12 Jun 2023 13:29:49 GMT

It didn't work as expected. In fact, the general total value is returning the sum of the TOP 3 products, but it is not returning the individual value of each of the TOP 3. I need to know the value of each of the TOP 3 products.

Re: TOP 3 measure not working

Anonymous — Tue, 13 Jun 2023 07:21:12 GMT

Hi Rai_BI ,

Can you please share a pbix or some dummy data that keep the raw data structure with expected results? It should help us clarify your scenario and test to coding formula.

How to Get Your Question Answered Quickly

Regards,

Xiaoxin Sheng

Re: TOP 3 measure not working

Rai_BI — Tue, 13 Jun 2023 13:35:01 GMT

Hi, I created a file to exemplify, because I cannot share the original file because it contains sensitive data. The link to download : https://we.tl/t-QKY402CEdC
I can´t attach a file here