Hi All,
I have a problem and looking for a solution for the same -
I have one variable name as Categories of furniture, initially I got the counts of this unique categories and then I ranked this variable based on counts. Now sorted teh same in descending order. The values whose rank is "1-3" the precode will remain same but from 4 till end rank the name of the precode will be "Others".
First I generated a Stacked chart keeping "Date in x-axis" and categories in Y-axis the data should be trailing 6 months (means if I click on slicer(Date) August'2022 then in chart x-axis bars should be Mar'22-Aug'22. And in Legends I have droppeed Rank Precode variable in whick based on the Latest month I will get Top 3 + 1 (Others) legends.
Problem:
1. Trailing 6 months chart
2. If I click on one legend (Suppose A) then based on the selection, other charts in the view should be changed. If I clieck on "Other" then also the other charts legends should change based on the selection
3. Point 2 is possible If I dnt have others
All other charts are also top 10, top 5, top 3 on different variables
Below are my DAX -
Calculated column DAX 1:
Categories = DISTINCT(FILTER(
VALUES('Data'[Categories]),
'Data'[Categories] <> "Null"))
Calculated column DAX 2:
Count of Categories =
CALCULATE (
COUNT('Data'[Categories ] ),
FILTER( 'Data', 'Data'[Categories ]='Categories'[Categories ])
Calculated column DAX 3:
Rank = RANKX('Categories', 'Categories'[Count of Categories])
DAX 4:
Calculated column DAX 4
Display Categories = IF('Categories'[Rank]>3, "Other", 'Categories'[Categories])
Measure
Trailing Last 6 months =
VAR Last6Months = MAX('Date'[SELECT MONTH])
VAR Previousdate = DATE(YEAR(Last6Months), MONTH(Last6Months)-6,DAY(Last6Months))
VAR Result = CALCULATE(
COUNT('Data'[Categories]),
FILTER(
'Data',
'Data'[SELECT MONTH] >= Previousdate && 'Data'[SELECT MONTH] <= Last6Months
)
)
RETURN Result
******
Stacked Chart
x-axis: Date
y-axis: Trailing Last 6 months
Legend: Display Categories
KIndly let me know if any solution is possible as I tried many things but couldn't achieved the result.
Also, I want legends order based on max count.
