Is there a way to sort a measure by topn both in asc order and desc order in a chart?

[vanessafvg]

I am trying to use the same bar graph to show either  top 10 or  bottom 10.  Not quite sure how to pull it al together using the same graph and samme measure, i.e the user should be able to switch between the views with a filter, either selectiing bottom 10 or top 10.

help please this is utterly frustrating 

[Vvelarde · Accepted solution]

@vanessafvg

One Way could be this:

Step 1: A Table With Top 10 and Bottom 10 Text. To use in slicer.

Step 2: Create a measure to Rank:

Rank =
IF (
    HASONEVALUE ( View[View] ),
    SWITCH (
        VALUES ( View[View] ),
        "Top 10", RANKX ( ALL ( Table1 ), CALCULATE ( SUM ( Table1[Points] ) ),, DESC; DENSE ),
        RANKX ( ALL ( Table1 ), CALCULATE ( SUM ( Table1[Points] ) ),, ASC; DENSE )
    )
)

Step 3: Use A chart and Apply a Visual Level Filter Rank less or equal to 10.

[nasheno]

What is the view/(view) and Table 1 points in the rank formula?

[jolsby]

Hi Vvelarde,

I have been trying to solve a similar issue, and have been spending much of the day trying to resolve this.

Would you be kind enough to send me a attachment of your Power BI? I have tried using your DAX, but alas, I am not getting the results I am expecting. 😞

Your results look to be what I would like! I appreciate the help!

All my best,

Jolsby

[vanessafvg]

@jolsby what is the problem you are having?  

[jolsby]

Hi @vanessafvg,

Thanks for your reply. For context, I am continuing from this article, which allows me to have dynamic meaures.

When I try to leverage the "Measure Selection" as referenced in the above article, for a Top 5 or Bottom 5, I have yet to get the logic exactly as I would like. If you would like to follow along, you can use the article for reference to my current state.

I have seen similar uses for using a table as a slicer like so. So here is my template.

Here is my calculation, you will see there are no errors.

I have set the visual level filter rank less than or equal to 5.I do not receive any results by selecting Bottom 5.I see all products by selecting Top 5.

Thus this is the outcome that I am recieving.

Best,

Jolsby

[vanessafvg]

@jolsby  nothing obvious sticks out to be honest.   are you absolutely sure the measure you selecting has a bottom ranking? ie i can't see the measure, have you got the right one in there.

The first thing i would say is remove everything and get to basics.

1. Create a bar graph and drag your measure on to it without the dynamic filtering. if everything looks fine ,

2. create 2 new individual ranking measures one for top and one for bottom and add them one at a time to the visual filters to check if the work.  if that works.. 

3. then add the top and bottom filters and the original rank measure back and see if that works

4. then add the dynamic filters

basically break it down until you can isolate where the issue is

[jolsby]

Hi Vanessa,

Thanks for the help!

I was able to figure it out here!

https://community.powerbi.com/t5/Data-Stories-Gallery/Executive-Insights-by-Decisive-Data/m-p/181742

Best,

Jake

[vanessafvg]

brilliant job thanks!