Total of TOPN Dax

DAX DAX Calculations
1

Hello everyone,
I’m trying to learn how to use dax for a TOPN products. So many videos but don’t know where to start.
But as i saw in this Sam’s video (https://www.youtube.com/watch?v=GgN5lwRUaqc) the total of that topn measure were the same as total sales. Question : how can i have the total for only the top 5 customers so i can see what they represent on all sales?
Thank you all
Best regards
Pedro

1 Like
2

@pedroccamara,

@BrianJ just did a video on RANKX on the Enterprise DNA YouTube channel. This will perform much better than the TOPN function.I’ll include a couple videos on TOPN as well.

Here are some other resources on TOPN and RANKX

https://info.enterprisedna.co/dax-function-guide/topn/
https://info.enterprisedna.co/dax-function-guide/rankx/

Thanks
Jarrett

3

@pedroccamara,

Lots of different ways to handle this. Here’s just one:

In the measure, my first variable creates the virtual table of top 5 customers using TOPN(5). The second variable just sums the total sales associated with this table:

Sum of TopN5 = 

VAR TopN5Cust =
TOPN(
    5,
    Customers,
    [Total Sales],
    DESC
)

VAR Result =
SUMX(
    TopN5Cust,
    [Total Sales]
)

RETURN
Result

image

I hope this is helpful. Full solution file attached.

1 Like
4

@JarrettM,

Just saw your response after posting mine. I’m curious - when you say RANKX will perform much better, did you mean that you thought it was better suited to solving @pedroccamara’s question or that literally doing RANKX, and filtering the RANKX results <= 5 would run faster than taking TOPN(5) and then iterating the sum of Total Sales?

  • Brian
5

@BrianJ,

Just thought it might be better suited for his solution.

Thanks
Jarrett

6

Hey Brian, i think this is what i need cose i already have the TOPN formula.
When i applied yours, like this:

Sum of Top 5 Products =
VAR TopN5Cust =
TOPN ( 5, Sales, Sales[Product], [T Sales], 0 )
VAR Result =
SUMX ( TopN5Cust, [T Sales] )
RETURN
Result
It shows this error Capture

7

I believe the formula above should be like this…:

Sum of Top 5 Products =
VAR TopN5Product =
TOPN ( 5, Sales, Sales[Product], DESC)
VAR Result =
SUMX ( TopN5Product, [T Sales] )
RETURN
Result

Now i gotta tell you that the row context is the same, products and filtered by year. So i guess, somewhere in this formula i should add ALL (Sales [Product]), right?

8

@pedroccamara,

While your implementation of the measure looks like mine, there is a very important distinction - you are filtering directly on your fact table (Sales), while I am filtering on my dimension table (Customers):

image

image

While it’s not an ironclad rule, a good general principle is that you want to aggregate on Fact tables, but filter on Dimension tables. This will result in both much better performance and more easily understood DAX.

You are absolutely correct that if you have filters or slicers operating on the relevant dimension table that you should wrap it in ALL or ALLSELECTED when the dimension table is called within TOPN.

Thus, to calculate total sales on Top 5 products, the measure just requires changing Customers to Sales in the table parameter of the TOPN() call in the first variable, which I’ve now called TopN5Prod:

Sum of TopN5 = 

VAR TopN5Prod =
TOPN(
    5,
    ALL(Products),
    [Total Sales],
    DESC
)

VAR Result =
SUMX(
    TopN5Prod,
    [Total Sales]
)

RETURN
Result

Hope this is helpful.

  • Brian
2 Likes
9

you’re the boss!!!
Thank you so much Brian

P.S. About that thing on fact and dimension table, i totally agree with you. This is just a test for a (hopefully) client of mine

Awesome Brian

10

@pedroccamara,

:+1: - glad we could help, and that you got what you needed.

  • Brian
1 Like
11

Perfect and it is quiet exclusive