Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi
I have a requirement to show Top N locations based on sum of Score.
I need a combo chart where the bars show the top N locations and the lines show a constant average running through only Top N bars but the average here is overall average of all the locations.
I also have a slicer with options, upon selecting one of the options, the Top N bars will be ordered by the value associated with that option
I also have 1 slicer to order the bars either ascending or descending.
I need the dax calculation to achieve the dynamic slicing for Top N along with Average lines.
Also in case of ties, if suppose I select Top 3 and I have 3 locations with same rank, only those 3 locations should appear in bar as the Top 3 and not the other locations.
If no slicer is selected by default it should show all locations in Ascending order, when only Top N value is given it should give Top N locations in Descending order based on score, when only one of the option is selected without Top N value given then it should show all the locations in Ascending order, when Top N value is given and one of the options selected (say PML) it should show Top N locations based on Score but ordered by say - Total PML Value (value associated with PML)
Solved! Go to Solution.
I was able to achieve the above said requirement with the following dax
Top N Measure =
Var
RankingDimension = Values('Table'[Location name])
Var
RankingSelect = selectedvalue('Top N RG'[Top N RG])
Var Option = SELECTEDVALUE('Top N Option Select'[Option])
Return
if(iscrossfiltered('Top N RG'[Top N RG]),
CALCULATE(sum(Score),
Filter(RankingDimension,
RANKX(All('Table'[Location name]),sum(Score),,DESC,Skip) <= RankingSelect )),
if(iscrossfiltered('Top N RG'[Top N RG]) && iscrossfiltered('Top N Option Select'[Option]),
switch(true(),
Option = "XML", CALCULATE(sum(Score), Filter(RankingDimension,
RANKX(All('Table'[Location name]),[Top Option Select1],,DESC,Skip) <= RankingSelect )),
option = "EML", CALCULATE(sum(Score), Filter(RankingDimension,
RANKX(All('Table'[Location name]),[Top Option Select2],,DESC,Skip) <= RankingSelect )),
option = "PML", CALCULATE(sum(Score), Filter(RankingDimension,
RANKX(All('Table'[Location name]),[Top Option Select3],,DESC,Skip) <= RankingSelect ))),
if(iscrossfiltered ('Top N Option Select'[Option]), sum(Score),
sum(Score))))
Here I have created following custom tables and measures
1. Top N RG = GENERATESERIES(1, 30, 1)
2. Top N Option Select table with column Option having values - XML,EML,PML
3. Measur- Top Option Select1 = if(SELECTEDVALUE('Top N Option Select'[Option])="XML", sum('Table'[Score']),BLANK())
similarly measures Top Option Select2 and Top Option Select3
Hope this helps.
Cheers!
I was able to achieve the above said requirement with the following dax
Top N Measure =
Var
RankingDimension = Values('Table'[Location name])
Var
RankingSelect = selectedvalue('Top N RG'[Top N RG])
Var Option = SELECTEDVALUE('Top N Option Select'[Option])
Return
if(iscrossfiltered('Top N RG'[Top N RG]),
CALCULATE(sum(Score),
Filter(RankingDimension,
RANKX(All('Table'[Location name]),sum(Score),,DESC,Skip) <= RankingSelect )),
if(iscrossfiltered('Top N RG'[Top N RG]) && iscrossfiltered('Top N Option Select'[Option]),
switch(true(),
Option = "XML", CALCULATE(sum(Score), Filter(RankingDimension,
RANKX(All('Table'[Location name]),[Top Option Select1],,DESC,Skip) <= RankingSelect )),
option = "EML", CALCULATE(sum(Score), Filter(RankingDimension,
RANKX(All('Table'[Location name]),[Top Option Select2],,DESC,Skip) <= RankingSelect )),
option = "PML", CALCULATE(sum(Score), Filter(RankingDimension,
RANKX(All('Table'[Location name]),[Top Option Select3],,DESC,Skip) <= RankingSelect ))),
if(iscrossfiltered ('Top N Option Select'[Option]), sum(Score),
sum(Score))))
Here I have created following custom tables and measures
1. Top N RG = GENERATESERIES(1, 30, 1)
2. Top N Option Select table with column Option having values - XML,EML,PML
3. Measur- Top Option Select1 = if(SELECTEDVALUE('Top N Option Select'[Option])="XML", sum('Table'[Score']),BLANK())
similarly measures Top Option Select2 and Top Option Select3
Hope this helps.
Cheers!
@Anonymous
This video explains a solution that should work for you:
https://www.youtube.com/watch?v=33k6LKvtJZ8
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
41 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
42 | |
33 | |
30 | |
18 | |
18 |