cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Super User
Super User

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

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 Man Frustrated


Did I answer your question? Mark my post as a solution.
Proud a to be a Datanaut!
1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

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

@vanessafvg

 

One Way could be this:

 

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

 

ViewTable.png

 

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.

 

Rank.png




Lima - Peru
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




8 REPLIES 8
Super User
Super User

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

@vanessafvg

 

One Way could be this:

 

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

 

ViewTable.png

 

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.

 

Rank.png




Lima - Peru
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




Super User
Super User

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

brilliant job thanks!


Did I answer your question? Mark my post as a solution.
Proud a to be a Datanaut!
jolsby Regular Visitor
Regular Visitor

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

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. Smiley Sad

 

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

 

All my best,

Jolsby

Super User
Super User

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

@jolsby what is the problem you are having?  


Did I answer your question? Mark my post as a solution.
Proud a to be a Datanaut!
jolsby Regular Visitor
Regular Visitor

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

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.

 

 

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

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

 

 

 

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

Thus this is the outcome that I am recieving.

 

Best,

Jolsby

Super User
Super User

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

@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

 

 

 

 


Did I answer your question? Mark my post as a solution.
Proud a to be a Datanaut!
jolsby Regular Visitor
Regular Visitor

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

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

nasheno Frequent Visitor
Frequent Visitor

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

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