Dax grand total issue

iP_123

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

Louis_Guionneau

New Member
Joined
Nov 12, 2019
Messages
28
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
Joined
Apr 18, 2016
Messages
95
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.
 

Forum statistics

Threads
1,082,244
Messages
5,363,981
Members
400,772
Latest member
solbebe

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top