cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ankita_92
Frequent Visitor

Dynamic Slicing on Top N

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)

1 ACCEPTED SOLUTION
ankita_92
Frequent Visitor

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!

View solution in original post

2 REPLIES 2
ankita_92
Frequent Visitor

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!

View solution in original post

Fowmy
Super User IV
Super User IV

@ankita_92 

This video explains a solution that should work for you: 

https://www.youtube.com/watch?v=33k6LKvtJZ8


Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.