Dax grand total issue

iP_123

Board Regular
Good afternoon,

I wrote a DAX formula to rank customer sales which works fine, however I want to bring in total profits for the corresponding ranked sales but my dax formula somehow shows the right values for the total profit of each customer sales but the grand total is incorrect. It shows the grand total amount for all the customers not the selected rank.
I would appreciate the help to correct my total profit dax formula for the selected rank.

The below ranks my customer sales (works fine)

Top Sales Customer =
VAR CustomerRank = RANKX( ALL( Customer[Customer Names]),
CALCULATE([Total Sales]), , DESC, Dense)
RETURN
IF(ISFILTERED(Customer[Customer Names] ),
IF( [Customer Sales Rank Desc] <= [Rank Select], [Total Sales], BLANK() ),
CALCULATE([Total Sales], TOPN( [Rank Select], VALUES( Customer[Customer Names] ), [Total Sales], DESC )))

The below Dax shows the total profits for the ranked customer sales but has the incorrect grand total;

The below Dax shows the total profits for the ranked customer sales but has the incorrect grand total;

Top Profits Customer =
IF(ISFILTERED(Customer[Customer Names] ),
IF( [Customer Sales Rank Desc] <= [Rank Select], [Total Profits], BLANK() ),
IF(HASONEVALUE(Customer[Customer Names]),
[Total Profits], SUMX(VALUES( Customer[Customer Names] ), [Total Profits])
))

Ip_123

Last edited:

Louis_Guionneau

New Member
iP - not sure if other folks are also having this issue, but it's not clear to me what you're trying to do exactly, based on your below sentence. Can you please clarify? Are you trying to rank customers by sales and then show corresponding profits for each customer? Don't understand.

I wrote a DAX formula to rank customer sales which works fine, however I want to bring in total profits for the corresponding ranked sales but my dax formula somehow shows the right values for the total profit of each customer sales but the grand total is incorrect.

iP_123

Board Regular
My apologies. Yes I'm trying to rank customers by sales and then show corresponding profits for each of the ranked customers. The first dax I posted earlier (Top Sales Customer) ranks the customer sales perfectly. However the second dax which is suppose to show the corresponding total profits of the ranked customer sales does not show the correct grand total however the customer profits in each row are correct.
I want to the second dax to show the correct customer profits (which it currently does) as well as the correct grand total (which it currently does not).
I apologize for any confusion in my initial request.
Thanks.