Unique Dax grand total incorrect

DAX DAX Calculations
1

I watched your tutorial on ranking customer sales which I was able to replicate for my purposes. The uniqu issue I have is when I bring in another measure which is to show the corresponding total profits for the selected customer sales, my profits for each customer is correct however the grand total is incorrect.

I need some assistance with this dax formula please. I’m sure I am missing or doing something wrond;

Dax to show the corresponding total profits for the selected customer sales below;

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])
))

Thanks

2

Welcome back to the forum. Please make sure that you do a search within the forum for the subject matter you are about to post before posting. There are numerous threads with this type of question. Here is a recent thread that has been discussed within the last week that will help point you in the right direction. More than likely you will need the SUMMARIZE function in DAX to get the correct total you are looking for. Here is the link to the thread:

Thanks
Enterprise%20DNA%20Expert%20-%20Small

3

Thank you for the reply. I did follow the instructions in the example you recommended and also did a search before submitting my query but it appears my profit totals are still coming up as incorrect.
Your help and or guidance is appreciated.

Thank you.

4

Please post a sample PBIX file so we can better assist.

Thanks
Enterprise%20DNA%20Expert%20-%20Small

5

Dynamically Change Size Of Visual Based On Ranking Selection in Power BI using DAX v2.pbix (663.7 KB)

Thank you for the consideration. The Top Profit Customer dax total is the issue I’m having. In short I’m hoping when I select any rank in the slicer both the Sales and Profit update accordingly.

Thank you again for taking the time Sir.

6

Here is solution I came up with that gets you the correct totals:
Top Profits Customer 2 =
SWITCH (
TRUE (),
[Top Sales Customer] = BLANK (), BLANK (),
[Top Sales Customer] <> BLANK (), CALCULATE ( [Total Profits], FILTER ( Customer, [Top Sales Customer] ) ),
BLANK ()
)

** These photos show that Totals still work even when you change the Rank at the top**

image
image1312×349 20.8 KB

image
image1318×353 24.3 KB

Ranking Totals Not Correct Solution- Enterprise DNA Forum.pbix (661.1 KB)

Thanks
Enterprise%20DNA%20Expert%20-%20Small

2 Likes
7

GM…

Thank you very much ojones! I truly appreciate your time and assistance. Your solution works PERFECT!

While looking into this myself last night I figured out the below DAX which works as well but not nearly as logical as yours;

Total Customer Profits =
SUMX( SUMMARIZE(
Customer, Customer[Customer Names] ,
“Virtual Table” , IF(ISFILTERED(Customer[Customer Names] ),
IF( [Customer Sales Rank Desc] <= [Rank Select], [Total Profits], BLANK() ),
[Total Profits] )), IF([Customer Sales Rank Desc] <= [Rank Select], [Total Profits],
BLANK()))

Thanks again kind Sir.

xZ.

8

Is there are to mark this problem SOLVED and give the expert that assisted a star rating?

9

Look at section 5 from the following link:
https://forum.enterprisedna.co/t/how-to-use-the-enterprise-dna-support-forum/3951

Thanks
Enterprise%20DNA%20Expert%20-%20Small

10

@quichi2010,

Here’s a screenshot that illustrates what @JarrettM referred to above.

image
image.png1897×1245 392 KB

With regard to your other question, there’s no star rating for solutions, but you can “like” any post by clicking on the heart icon at the bottom of that post, and the person whose post is “liked” will get a notification.

image
image.png919×379 83.5 KB

  • Brian
11

@quichi2010, a response to this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the checkbox. Thanks!"