Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

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 ...
Need Help
Show and Tell
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi Anonymous ,

     

    Please check if this could meet your requirements.

     

     

    1. Enter data to create a Rank Table.

     

    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:

     

    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.

     

collinq's avatar
collinq
Icon for Super User rankSuper User
5 years ago

HI Anonymous ,

 

 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!

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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)

     

    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)