EDNA Forum Good Day
I hope this email finds you all well.
I need some direction on creating a Dynamic Dax Measure that will give me the
Top 25 customers and Bottom 25 customers by TTM
And
Top 25 products and Bottom 25 products by TTM
Also is there a way to create a dynamic label in the title of a Matrix?
Please contact me with any questions
Thank you
Attachment:
Please review the PBIX file and check the measures.
Dax Calculation Example 03 15 21 - JoseBressan.pbix (2.2 MB)
Hello jbressan
Can you send me a screen shot of the Dax measures I’m having an issue opening the pbix.
Also re: the Dax measure for product if I want to include the product and the customers for the product how would I modify the Dax measure?
Thank you
Jbressan
Please got the file to open
Thank you
Hello jbressan
In what order should I create the measures and the tables?
Measures
RankDisplay measures
RankDisplay
there is a RankDisplay with a ? mark
Top or Bottom table?
1.- Create Table Top or Bottom (It will be used as a slicer)
2.- Create What If Parameters RankDisplay (It will be used as a slicer)
3.- Create a Measure for Ranking
TTM = SUM(Sheet1[CY TTM $])
4.- Create a Measure Customer N
Customer N =
VAR _TopN = [RankDisplay Value]
VAR _Top_or_Bottom =
SELECTEDVALUE ( 'Top or Bottom'[Top or Bottom] )
VAR _Rank_Top =
IF (
ISINSCOPE ( Sheet1[Customer ] ) && HASONEVALUE ( Sheet1[Customer ] ),
RANKX (
CALCULATETABLE (
VALUES ( Sheet1[Customer ] ),
ALLSELECTED ( Sheet1[Customer ] )
),
[TTM],
,
DESC
)
)
VAR _Rank_Bottom =
IF (
ISINSCOPE ( Sheet1[Customer ] ) && HASONEVALUE ( Sheet1[Customer ] ),
RANKX (
CALCULATETABLE (
VALUES ( Sheet1[Customer ] ),
ALLSELECTED ( Sheet1[Customer ] )
),
[TTM],
,
ASC
)
)
VAR Result_Top =
SWITCH ( TRUE (), _TopN = 0, [TTM], _Rank_Top <= _TopN, [TTM] )
VAR Result_Bottom =
SWITCH ( TRUE (), _TopN = 0, [TTM], _Rank_Bottom <= _TopN, [TTM] )
RETURN
IF ( _Top_or_Bottom = "Top", Result_Top, Result_Bottom )
5.- Create a Measure Customer Display N (This is the measure that you will use in the visualizations and you can rename it whatever you want. Validate that the Dimension (Customer) is in rank)
Customer Display N =
IF(
COUNTROWS(VALUES(Sheet1[Customer ]))=1,
[Customer N],
SUMX(VALUES(Sheet1[Customer ]),[Customer N]))Hi jbressan
Understood, what time zone are you in?
Thank you
I’m United States Eastern Standard Time zone (EST) or UTC/GMT -5
Hi jbressan
same here…
Will send over questions as they come up. I don’t see Single Value as an option for My Rank Display.
This is the default setup. Switch to one of the others to see the difference.
Not sure what you mean. Switch to one of the others to see the difference.
This is my view from my Rank Display. I don’t have Single Value as an options.
Hi jbressan
Per my screenshots,
I’m showing Top 5 for Customer,s on my matrix and table
I’m showing ALL products - for Products. on y matrix and table
How should I troubleshoot the product Matrix and Table?
So far I have checked the following:
Product N Measure, everything look good,
Will continue to check all product related measures and tables.
Also
When I change Rank Display to 10, there is no change in the Customer matrix and table, it’s stuck on 5
No change in my Product Matrix and Table as well
thank you
Hi jbressan
Fix the Product Display N count
Still have the following issue.
Product Display N and Customer Display N - still struck on count 5
Make sure the RankDisplay is setup like single value
jbressan
How do I get the Top 20 to Display in the Matrix, It’s not working in my reports? What should I check?
thank you
