Hello again,
I used your measures and modified them (i basically added the measures for CY TTM$ and PY TTM $ with some logic instead of using the calculated colums > you will do the same for the remaining columns), and i still have to fix the totals (which won’t be that i hard i suppose. But i think when it comes to performance, your method might cause performance issues (copy of page 1).
That’s why i tried to come up with another solutions (page : TOP 10), but i still have 2 issues there :
- sometimes it doesn’t give exactly the top N chosen (9 instead of 10).
i’m still trying to find an answer to that. Hope this gives you another vision at least. I’ll try to find an anwser for these issues.
Dax Measure Example 03 18 21 TNBN_Display Modification.pbix (2.2 MB)
Hi Hassan
Will review the updated pbix.
Much appreciate your help!
Thank you!
Regards,
Hi Hassan
The new pbix. definitely gets me closer to the TTM view I need to see without showing the other customers that fall outside of RankDisplay Selected in this case 10. I understand what you mean by having performance issues with the current setup. I have to think of a different approach to make the process and reporting more efficient. I will send you another demo pbix. to see if we can put I heads together to think of a different method. You have solved my ask for this post. If I initiate another post can you pick it up, not certain how the posting works on the forum.
Thank you!
Also, forgot to mention. I saw the other method - measures you suggested will give that a try as well.
Thank you
Hassan
Re: the other method here, will customer and products outside the RankDisplay selected be excluded in the visual?
Products Ranking by YOY TTM =
VAR Top_Or_Bottom = SELECTEDVALUE(‘Top or Bottom’[Top or Bottom])
VAR TOP_N =
CALCULATE(
[YOY TTM],
TOPN([RankDisplay Value], ALL(Sheet1), [YOY TTM], DESC),
VALUES(Sheet1[Product]),
VALUES(Sheet1[Customer ]) )
VAR BOTTOM_N =
CALCULATE(
[YOY TTM],
TOPN([RankDisplay Value], ALL(Sheet1), [YOY TTM], ASC),
VALUES(Sheet1[Product]),
VALUES(Sheet1[Customer ]) )
RETURN
IF(Top_Or_Bottom = “Top”,
TOP_N, BOTTOM_N )
Hello again,
This measure will show you the the TOP / BOTTOM Products and customers depending on the RankDisplay Value
in the function TOPN, the first argument represent the number ypu want to show (Top 5, 10…). Instead of using a fixed number, i used the measure [RankDisplay Value] So that the the number is dynamic depending on what you want to see.
Hello Hassan
I will take another look.
Thank you
Re: PBIX attached tab Top 10
I made a change to the Display measure below, and added a Slicer for Product to the visual.
Display Title = SELECTEDVALUE(‘Top or Bottom’[Top or Bottom]) & " " & [RankDisplay Value] & " " & SELECTEDVALUE(Sheet1[Product])
**** How can I add ( All Products) to the Display Title when nothing is selected for the 3 slicer?***
Top or Bottom
Product
RankDisplay
Thank you
Attachment:
P.S. - What time zone are you in?
Dax Measure Example 03 18 21 TNBN_Display Modification.pbix (2.2 MB)
Hello Hassan
Can we exchange personal email address? I want to get your feedback on my communication through the EDNA forum. Want to make certain I’m being concise and clear as to what I’m trying to accomplish per my post. I’m always open to feedback that’s how you learn.
Thank you
Hi,
@ambidextrousmentally,
Yeah sure, you can leave your email in the comment.
Hello Hassan
I tried the other method with a different data table - Table Name PBIX. With this new setup, how would I use the other method measures?
Also, if I clear the selections for the following:
Rank Display
Top or Bottom
Product Name
Is there a way to put in the Display Titles for the Matrix below : All Customers and All Products
Customer Matrix - All Customers
Product Matrix - All Product
Thank You
Attachment:
PBIXDD.pbix (2.4 MB)