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 basically, looking at the image below you can see that Monday 2pm, 3pm and 5pm are the highest numbers. So the results should be as per below: Resulting Table Should be:DayTimeTopMonday2pm3Monday3pm3Monday5pm3Thursday2pm3Friday4pm2.83 Any assistance? EDIT:This is the data from the tables:storeideventdateEventfromTimedaynamestafftypeCustomer VolumeCustomer to Staff RatioEventfromTime (bins)Hour of Day246617 Dec 20174:00:00 PMSun1staff224:00:00 PM16246617 Dec 20174:05:00 PMSun1staff224:00:00 PM16246617 Dec 20174:10:00 PMSun1staff114:00:00 PM16246617 Dec 20174:15:00 PMSun1staff114:00:00 PM16246617 Dec 20174:20:00 PMSun1staff224:00:00 PM16246617 Dec 20174:25:00 PMSun1staff114:00:00 PM16246617 Dec 20174:30:00 PMSun1staff114:00:00 PM16246617 Dec 20174:35:00 PMSun1staff114:00:00 PM16246617 Dec 20174:40:00 PMSun1staff114:00:00 PM16246617 Dec 20174:45:00 PMSun1staff004:00:00 PM16246617 Dec 20174:50:00 PMSun1staff004:00:00 PM16246617 Dec 20174:55:00 PMSun1staff004:00:00 PM16246617 Dec 20175:00:00 PMSun1staff115:00:00 PM17246617 Dec 20175:05:00 PMSun1staff115:00:00 PM17246617 Dec 20175:10:00 PMSun1staff225:00:00 PM17246617 Dec 20175:15:00 PMSun1staff115:00:00 PM17246617 Dec 20175:20:00 PMSun1staff115:00:00 PM17246617 Dec 20175:25:00 PMSun1staff225:00:00 PM17246617 Dec 20175:30:00 PMSun1staff115:00:00 PM17246617 Dec 20175:35:00 PMSun1staff225:00:00 PM17246617 Dec 20175:40:00 PMSun1staff225:00:00 PM17246617 Dec 20175:45:00 PMSun1staff225:00:00 PM17246617 Dec 20175:50:00 PMSun1staff445:00:00 PM17246617 Dec 20175:55:00 PMSun1staff115:00:00 PM17

Advocate 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 basically, looking at the image below you can see that Monday 2pm, 3pm and 5pm are the highest numbers. So the results should be as per below:

Resulting Table Should be:

Day	Time	Top
Monday	2pm	3
Monday	3pm	3
Monday	5pm	3
Thursday	2pm	3
Friday	4pm	2.83

Any assistance?

EDIT:

This is the data from the tables:

storeid	eventdate	EventfromTime	dayname	staff	type	Customer Volume	Customer to Staff Ratio	EventfromTime (bins)	Hour of Day
2466	17 Dec 2017	4:00:00 PM	Sun	1	staff	2	2	4:00:00 PM	16
2466	17 Dec 2017	4:05:00 PM	Sun	1	staff	2	2	4:00:00 PM	16
2466	17 Dec 2017	4:10:00 PM	Sun	1	staff	1	1	4:00:00 PM	16
2466	17 Dec 2017	4:15:00 PM	Sun	1	staff	1	1	4:00:00 PM	16
2466	17 Dec 2017	4:20:00 PM	Sun	1	staff	2	2	4:00:00 PM	16
2466	17 Dec 2017	4:25:00 PM	Sun	1	staff	1	1	4:00:00 PM	16
2466	17 Dec 2017	4:30:00 PM	Sun	1	staff	1	1	4:00:00 PM	16
2466	17 Dec 2017	4:35:00 PM	Sun	1	staff	1	1	4:00:00 PM	16
2466	17 Dec 2017	4:40:00 PM	Sun	1	staff	1	1	4:00:00 PM	16
2466	17 Dec 2017	4:45:00 PM	Sun	1	staff	0	0	4:00:00 PM	16
2466	17 Dec 2017	4:50:00 PM	Sun	1	staff	0	0	4:00:00 PM	16
2466	17 Dec 2017	4:55:00 PM	Sun	1	staff	0	0	4:00:00 PM	16
2466	17 Dec 2017	5:00:00 PM	Sun	1	staff	1	1	5:00:00 PM	17
2466	17 Dec 2017	5:05:00 PM	Sun	1	staff	1	1	5:00:00 PM	17
2466	17 Dec 2017	5:10:00 PM	Sun	1	staff	2	2	5:00:00 PM	17
2466	17 Dec 2017	5:15:00 PM	Sun	1	staff	1	1	5:00:00 PM	17
2466	17 Dec 2017	5:20:00 PM	Sun	1	staff	1	1	5:00:00 PM	17
2466	17 Dec 2017	5:25:00 PM	Sun	1	staff	2	2	5:00:00 PM	17
2466	17 Dec 2017	5:30:00 PM	Sun	1	staff	1	1	5:00:00 PM	17
2466	17 Dec 2017	5:35:00 PM	Sun	1	staff	2	2	5:00:00 PM	17
2466	17 Dec 2017	5:40:00 PM	Sun	1	staff	2	2	5:00:00 PM	17
2466	17 Dec 2017	5:45:00 PM	Sun	1	staff	2	2	5:00:00 PM	17
2466	17 Dec 2017	5:50:00 PM	Sun	1	staff	4	4	5:00:00 PM	17
2466	17 Dec 2017	5:55:00 PM	Sun	1	staff	1	1	5:00:00 PM	17

Need Help

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

6 Replies

v-yulgu-msft

Microsoft 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

duggy

Advocate II

7 years ago

Hi,

Here is the table broken down:

storeid	eventdate	EventfromTime	dayname	staff	type	Customer Volume	Customer to Staff Ratio	EventfromTime (bins)	Hour of Day
2466	17 Dec 2017	4:00:00 PM	Sun	1	staff	2	2	4:00:00 PM	16
2466	17 Dec 2017	4:05:00 PM	Sun	1	staff	2	2	4:00:00 PM	16
2466	17 Dec 2017	4:10:00 PM	Sun	1	staff	1	1	4:00:00 PM	16
2466	17 Dec 2017	4:15:00 PM	Sun	1	staff	1	1	4:00:00 PM	16
2466	17 Dec 2017	4:20:00 PM	Sun	1	staff	2	2	4:00:00 PM	16
2466	17 Dec 2017	4:25:00 PM	Sun	1	staff	1	1	4:00:00 PM	16
2466	17 Dec 2017	4:30:00 PM	Sun	1	staff	1	1	4:00:00 PM	16
2466	17 Dec 2017	4:35:00 PM	Sun	1	staff	1	1	4:00:00 PM	16
2466	17 Dec 2017	4:40:00 PM	Sun	1	staff	1	1	4:00:00 PM	16
2466	17 Dec 2017	4:45:00 PM	Sun	1	staff	0	0	4:00:00 PM	16
2466	17 Dec 2017	4:50:00 PM	Sun	1	staff	0	0	4:00:00 PM	16
2466	17 Dec 2017	4:55:00 PM	Sun	1	staff	0	0	4:00:00 PM	16
2466	17 Dec 2017	5:00:00 PM	Sun	1	staff	1	1	5:00:00 PM	17
2466	17 Dec 2017	5:05:00 PM	Sun	1	staff	1	1	5:00:00 PM	17
2466	17 Dec 2017	5:10:00 PM	Sun	1	staff	2	2	5:00:00 PM	17
2466	17 Dec 2017	5:15:00 PM	Sun	1	staff	1	1	5:00:00 PM	17
2466	17 Dec 2017	5:20:00 PM	Sun	1	staff	1	1	5:00:00 PM	17
2466	17 Dec 2017	5:25:00 PM	Sun	1	staff	2	2	5:00:00 PM	17
2466	17 Dec 2017	5:30:00 PM	Sun	1	staff	1	1	5:00:00 PM	17
2466	17 Dec 2017	5:35:00 PM	Sun	1	staff	2	2	5:00:00 PM	17
2466	17 Dec 2017	5:40:00 PM	Sun	1	staff	2	2	5:00:00 PM	17
2466	17 Dec 2017	5:45:00 PM	Sun	1	staff	2	2	5:00:00 PM	17
2466	17 Dec 2017	5:50:00 PM	Sun	1	staff	4	4	5:00:00 PM	17
2466	17 Dec 2017	5:55:00 PM	Sun	1	staff	1	1	5:00:00 PM	17

duggy

Advocate II

7 years ago

Hi,

Here it is below:

storeid	eventdate	EventfromTime	dayname	staff	type	Customer Volume	Customer to Staff Ratio	EventfromTime (bins)	Hour of Day
2466	17 Dec 2017	4:00:00 PM	Sun	1	staff	2	2	4:00:00 PM	16
2466	17 Dec 2017	4:05:00 PM	Sun	1	staff	2	2	4:00:00 PM	16
2466	17 Dec 2017	4:10:00 PM	Sun	1	staff	1	1	4:00:00 PM	16
2466	17 Dec 2017	4:15:00 PM	Sun	1	staff	1	1	4:00:00 PM	16
2466	17 Dec 2017	4:20:00 PM	Sun	1	staff	2	2	4:00:00 PM	16
2466	17 Dec 2017	4:25:00 PM	Sun	1	staff	1	1	4:00:00 PM	16
2466	17 Dec 2017	4:30:00 PM	Sun	1	staff	1	1	4:00:00 PM	16
2466	17 Dec 2017	4:35:00 PM	Sun	1	staff	1	1	4:00:00 PM	16
2466	17 Dec 2017	4:40:00 PM	Sun	1	staff	1	1	4:00:00 PM	16
2466	17 Dec 2017	4:45:00 PM	Sun	1	staff	0	0	4:00:00 PM	16
2466	17 Dec 2017	4:50:00 PM	Sun	1	staff	0	0	4:00:00 PM	16
2466	17 Dec 2017	4:55:00 PM	Sun	1	staff	0	0	4:00:00 PM	16
2466	17 Dec 2017	5:00:00 PM	Sun	1	staff	1	1	5:00:00 PM	17
2466	17 Dec 2017	5:05:00 PM	Sun	1	staff	1	1	5:00:00 PM	17
2466	17 Dec 2017	5:10:00 PM	Sun	1	staff	2	2	5:00:00 PM	17
2466	17 Dec 2017	5:15:00 PM	Sun	1	staff	1	1	5:00:00 PM	17
2466	17 Dec 2017	5:20:00 PM	Sun	1	staff	1	1	5:00:00 PM	17
2466	17 Dec 2017	5:25:00 PM	Sun	1	staff	2	2	5:00:00 PM	17
2466	17 Dec 2017	5:30:00 PM	Sun	1	staff	1	1	5:00:00 PM	17
2466	17 Dec 2017	5:35:00 PM	Sun	1	staff	2	2	5:00:00 PM	17
2466	17 Dec 2017	5:40:00 PM	Sun	1	staff	2	2	5:00:00 PM	17
2466	17 Dec 2017	5:45:00 PM	Sun	1	staff	2	2	5:00:00 PM	17
2466	17 Dec 2017	5:50:00 PM	Sun	1	staff	4	4	5:00:00 PM	17
2466	17 Dec 2017	5:55:00 PM	Sun	1	staff	1	1	5:00:00 PM	17

duggy
Advocate II
7 years ago
hi,

Did you see the data tables?

v-yulgu-msft

Microsoft Employee

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

duggy
Advocate II
7 years ago
absolutely bloody brilliant!!!!

Just one change:

"_median", MEDIAN ( Dataset3[Hour of Day] )
to:
"_median", MEDIAN ( Customer to Staff Ratio] )

Many many thanks, would never have gotten there without the support!

Forum Discussion

Ordering by highest value oer day

6 Replies

Related content

2nd Highest Value

Find Highest Value of a Column

Highest value in a day

Create Measure to Return Highest Value

highest value by category