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])
))
I appreciate your help and time in advace.
Ip_123
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])
))
I appreciate your help and time in advace.
Ip_123
Last edited: