Dynamic Slicing on Top N

[Anonymous]

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)

[Anonymous · Accepted 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!

[Fowmy]

@Anonymous 

This video explains a solution that should work for you: 

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