Forum Discussion

duggy's avatar
duggy
Icon for Advocate II rankAdvocate II
7 years ago
Solved

Ordering by highest value oer day

Hi,   I have a table with medians for the given hour per day (see image below). I am looking to give the top 5 times (hours and day) where the median is the highest number for that week . So basica...
Need Help
  • v-yulgu-msft's avatar
    v-yulgu-msft
    7 years ago

    Hi duggy ,

     

    Use a Table visual to display data and add below [Rank] measure into visual level filter.

    rank =
VAR Curr_dayname =
    MAX ( Dataset3[dayname] )
VAR Curr_Event =
    SELECTEDVALUE ( Dataset3[EventfromTime (bins)] )
VAR Temp_Tab =
    ALLSELECTED ( Dataset3 )
VAR Temp_Tab1 =
    SUMMARIZE (
        Temp_Tab,
        Dataset3[dayname],
        Dataset3[EventfromTime (bins)],
        "_median", MEDIAN ( Dataset3[Hour of Day] )
    )
VAR Temp_Tab2 =
    ADDCOLUMNS ( Temp_Tab1, "ARANK", RANKX ( Temp_Tab1, [_median] ) )
RETURN
    MAXX (
        FILTER (
            Temp_Tab2,
            Dataset3[dayname] = Curr_dayname
                && Dataset3[EventfromTime (bins)] = Curr_Event
        ),
        [ARANK]
    )

     

    Best regards,

    Yuliana Gu

v-yulgu-msft's avatar
v-yulgu-msft
Icon for Microsoft Employee rankMicrosoft Employee
7 years ago

Hi duggy,

 

 

Is the source table formatted as above image? That is to say, there existing columns [EventfromTime], [Sun], [Mon], etc. If not, please show us the source table structure, including column headers and record rows.

 

Regards,

Yuliana Gu