Dax grand total issue

iP_123

Board Regular
Joined
Apr 18, 2016
Messages
99
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
 
Last edited:

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,223,893
Messages
6,175,241
Members
452,622
Latest member
Laura_PinksBTHFT

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top