cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper III
Helper III

Data order in visualisation

Hello

 

I have a chart similar to the one below where I want the 'category' (in this case 'watch a movie', 'stay home' and 'play sport') to be ranked from highest to lowest depending on the latest month's data. So as shown, in Sept, the order is correct, where as if we were looking at the chart in Aug, 'stay at home' with 17% would be displayed first (and consequently, if it was July, 'stay at home' would have been 3rd on the list)

 

Thanks in advance

 

aaronvincentnz_0-1605650351476.png

 

 

1 ACCEPTED SOLUTION
Microsoft
Microsoft

Hi @aaronvincentnz ,

 

Please check if this could meet your requirements.

4.png

 

 

1. Enter data to create a Rank Table.

5.png

 

2. Create measures.

Measure = 
VAR A_ =
    CALCULATE ( SUM ( 'Table'[Percent] ), 'Table'[Name] = "A" )
VAR B_ =
    CALCULATE ( SUM ( 'Table'[Percent] ), 'Table'[Name] = "B" )
VAR C_ =
    CALCULATE ( SUM ( 'Table'[Percent] ), 'Table'[Name] = "C" )

VAR M1 =
    UNION (
        ROW ( "Percent", A_, "Name", "A" ),
        ROW ( "Percent", B_, "Name", "B" ),
        ROW ( "Percent", C_, "Name", "C" )
    )
VAR Result1 =
    MAXX ( M1, [Percent] )
VAR Result2 =
    MINX ( TOPN ( 2, M1, [Percent] ), [Percent] )
VAR Result3 =
    MINX ( M1, [Percent] )
RETURN
    SWITCH (
        MAX ( 'Rank'[Rank] ),
        "1st", Result1,
        "2nd", Result2,
        "3rd", Result3
    )
Measure Tooltip = 
VAR A_ =
    CALCULATE ( SUM ( 'Table'[Percent] ), 'Table'[Name] = "A" )
VAR B_ =
    CALCULATE ( SUM ( 'Table'[Percent] ), 'Table'[Name] = "B" )
VAR C_ =
    CALCULATE ( SUM ( 'Table'[Percent] ), 'Table'[Name] = "C" )

VAR M1 =
    UNION (
        ROW ( "Percent", A_, "Name", "A" ),
        ROW ( "Percent", B_, "Name", "B" ),
        ROW ( "Percent", C_, "Name", "C" )
    )
VAR Result1 =
    MAXX ( M1, [Percent] )
VAR Result2 =
    MINX ( TOPN ( 2, M1, [Percent] ), [Percent] )
VAR Result3 =
    MINX ( M1, [Percent] )
VAR Tooltip1 =
    MAXX ( FILTER ( M1, [Percent] = Result1 ), [Name] )
VAR Tooltip2 =
    MAXX ( FILTER ( M1, [Percent] = Result2 ), [Name] )
VAR Tooltip3 =
    MAXX ( FILTER ( M1, [Percent] = Result3 ), [Name] )
RETURN
    SWITCH (
        MAX ( 'Rank'[Rank] ),
        "1st", Tooltip1,
        "2nd", Tooltip2,
        "3rd", Tooltip3
    )

 

Then, you can get this:

6.png

 

You can check more details from here.

 

 

 

Best Regards,

Stephen Tao

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

View solution in original post

4 REPLIES 4
Microsoft
Microsoft

Hi @aaronvincentnz ,

 

Please check if this could meet your requirements.

4.png

 

 

1. Enter data to create a Rank Table.

5.png

 

2. Create measures.

Measure = 
VAR A_ =
    CALCULATE ( SUM ( 'Table'[Percent] ), 'Table'[Name] = "A" )
VAR B_ =
    CALCULATE ( SUM ( 'Table'[Percent] ), 'Table'[Name] = "B" )
VAR C_ =
    CALCULATE ( SUM ( 'Table'[Percent] ), 'Table'[Name] = "C" )

VAR M1 =
    UNION (
        ROW ( "Percent", A_, "Name", "A" ),
        ROW ( "Percent", B_, "Name", "B" ),
        ROW ( "Percent", C_, "Name", "C" )
    )
VAR Result1 =
    MAXX ( M1, [Percent] )
VAR Result2 =
    MINX ( TOPN ( 2, M1, [Percent] ), [Percent] )
VAR Result3 =
    MINX ( M1, [Percent] )
RETURN
    SWITCH (
        MAX ( 'Rank'[Rank] ),
        "1st", Result1,
        "2nd", Result2,
        "3rd", Result3
    )
Measure Tooltip = 
VAR A_ =
    CALCULATE ( SUM ( 'Table'[Percent] ), 'Table'[Name] = "A" )
VAR B_ =
    CALCULATE ( SUM ( 'Table'[Percent] ), 'Table'[Name] = "B" )
VAR C_ =
    CALCULATE ( SUM ( 'Table'[Percent] ), 'Table'[Name] = "C" )

VAR M1 =
    UNION (
        ROW ( "Percent", A_, "Name", "A" ),
        ROW ( "Percent", B_, "Name", "B" ),
        ROW ( "Percent", C_, "Name", "C" )
    )
VAR Result1 =
    MAXX ( M1, [Percent] )
VAR Result2 =
    MINX ( TOPN ( 2, M1, [Percent] ), [Percent] )
VAR Result3 =
    MINX ( M1, [Percent] )
VAR Tooltip1 =
    MAXX ( FILTER ( M1, [Percent] = Result1 ), [Name] )
VAR Tooltip2 =
    MAXX ( FILTER ( M1, [Percent] = Result2 ), [Name] )
VAR Tooltip3 =
    MAXX ( FILTER ( M1, [Percent] = Result3 ), [Name] )
RETURN
    SWITCH (
        MAX ( 'Rank'[Rank] ),
        "1st", Tooltip1,
        "2nd", Tooltip2,
        "3rd", Tooltip3
    )

 

Then, you can get this:

6.png

 

You can check more details from here.

 

 

 

Best Regards,

Stephen Tao

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

View solution in original post

Thanks Stephen, whilst that solution isn't what I'm after, it will come in handy for something else I'm working on!

Super User II
Super User II

HI @aaronvincentnz ,

 

 You have the category going across and then you are adding time.  So, the visual is correct - it is telling you that for the category of "Watching a Movie" that for the three months in question these are the results.

 

If you are wanting to show the months by category you would need to flip this around.  Make the Months in the category and the three categories as the axis.  That way, you will see the top three things that happened in September.  This way, you would see in Sept that it went Watch a Movie, Stay at Home, Play a Sport.  And, for August you would see, Stay at Home, Watch a Movie,Play a SPort.

 

I would appreciate Kudos if my response was helpful. I would also appreciate it if you would Mark this As a Solution if it solved the problem. Thanks!




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

Proud to be a Datanaut!
Private message me for consulting or training needs.




I have the 'reverse' as you've described with the month's on the X-axis, but just not sure how to sort it so the categories (movie/home/sport) are displayed in descending order. I prefer to have it displayed as I have in the screenshot above as you can easily tell which are the top categories whereas doing it with the month's on the X-axis, it's not as clear which are the top categories (if that makes sense)

 

aaronvincentnz_0-1605658128914.png

These charts have been created in excel to illustrate my request, but if im asked which are the top categories overall (or lets say which are the most important categories), it's hard to know using the 'month' chart above (if that makes sense)

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Wave Release 2

Check out the updates in Power BI.

Overview of Power BI 2020 release wave 2!

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors