Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
duggy
Advocate II
Advocate II

Ordering by highest value oer day

‎02-01-2019 02:46 PM

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:

Capture.JPG

 

Resulting Table Should be:

DayTimeTop
Monday2pm3
Monday3pm3
Monday5pm3
Thursday2pm3
Friday4pm2.83

 

 Any assistance?

 

EDIT:

This is the data from the tables:

storeideventdateEventfromTimedaynamestafftypeCustomer VolumeCustomer to Staff RatioEventfromTime (bins)Hour of Day
246617 Dec 20174:00:00 PMSun1staff224:00:00 PM16
246617 Dec 20174:05:00 PMSun1staff224:00:00 PM16
246617 Dec 20174:10:00 PMSun1staff114:00:00 PM16
246617 Dec 20174:15:00 PMSun1staff114:00:00 PM16
246617 Dec 20174:20:00 PMSun1staff224:00:00 PM16
246617 Dec 20174:25:00 PMSun1staff114:00:00 PM16
246617 Dec 20174:30:00 PMSun1staff114:00:00 PM16
246617 Dec 20174:35:00 PMSun1staff114:00:00 PM16
246617 Dec 20174:40:00 PMSun1staff114:00:00 PM16
246617 Dec 20174:45:00 PMSun1staff004:00:00 PM16
246617 Dec 20174:50:00 PMSun1staff004:00:00 PM16
246617 Dec 20174:55:00 PMSun1staff004:00:00 PM16
246617 Dec 20175:00:00 PMSun1staff115:00:00 PM17
246617 Dec 20175:05:00 PMSun1staff115:00:00 PM17
246617 Dec 20175:10:00 PMSun1staff225:00:00 PM17
246617 Dec 20175:15:00 PMSun1staff115:00:00 PM17
246617 Dec 20175:20:00 PMSun1staff115:00:00 PM17
246617 Dec 20175:25:00 PMSun1staff225:00:00 PM17
246617 Dec 20175:30:00 PMSun1staff115:00:00 PM17
246617 Dec 20175:35:00 PMSun1staff225:00:00 PM17
246617 Dec 20175:40:00 PMSun1staff225:00:00 PM17
246617 Dec 20175:45:00 PMSun1staff225:00:00 PM17
246617 Dec 20175:50:00 PMSun1staff445:00:00 PM17
246617 Dec 20175:55:00 PMSun1staff115:00:00 PM17
Labels:
Message 1 of 7
1,050 Views
0
1 ACCEPTED SOLUTION
v-yulgu-msft
Employee
Employee

‎02-18-2019 12:41 AM

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]
    )

1.PNG

 

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
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

Message 6 of 7
953 Views
0
6 REPLIES 6
v-yulgu-msft
Employee
Employee

‎02-18-2019 12:41 AM

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]
    )

1.PNG

 

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Message 6 of 7
954 Views
0
duggy
Advocate II
Advocate II
In response to v-yulgu-msft

‎02-22-2019 02:10 PM

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!

Message 7 of 7
938 Views
0
v-yulgu-msft
Employee
Employee

‎02-04-2019 05:16 PM

Hi @duggy,

 

Capture.JPG

 

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

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Message 2 of 7
996 Views
0
duggy
Advocate II
Advocate II
In response to v-yulgu-msft

‎02-10-2019 11:05 PM

hi,

 

Did you see the data tables?

Message 5 of 7
965 Views
0
duggy
Advocate II
Advocate II
In response to v-yulgu-msft

‎02-05-2019 09:37 AM

Hi,

 

Here it is below:

storeideventdateEventfromTimedaynamestafftypeCustomer VolumeCustomer to Staff RatioEventfromTime (bins)Hour of Day
246617 Dec 20174:00:00 PMSun1staff224:00:00 PM16
246617 Dec 20174:05:00 PMSun1staff224:00:00 PM16
246617 Dec 20174:10:00 PMSun1staff114:00:00 PM16
246617 Dec 20174:15:00 PMSun1staff114:00:00 PM16
246617 Dec 20174:20:00 PMSun1staff224:00:00 PM16
246617 Dec 20174:25:00 PMSun1staff114:00:00 PM16
246617 Dec 20174:30:00 PMSun1staff114:00:00 PM16
246617 Dec 20174:35:00 PMSun1staff114:00:00 PM16
246617 Dec 20174:40:00 PMSun1staff114:00:00 PM16
246617 Dec 20174:45:00 PMSun1staff004:00:00 PM16
246617 Dec 20174:50:00 PMSun1staff004:00:00 PM16
246617 Dec 20174:55:00 PMSun1staff004:00:00 PM16
246617 Dec 20175:00:00 PMSun1staff115:00:00 PM17
246617 Dec 20175:05:00 PMSun1staff115:00:00 PM17
246617 Dec 20175:10:00 PMSun1staff225:00:00 PM17
246617 Dec 20175:15:00 PMSun1staff115:00:00 PM17
246617 Dec 20175:20:00 PMSun1staff115:00:00 PM17
246617 Dec 20175:25:00 PMSun1staff225:00:00 PM17
246617 Dec 20175:30:00 PMSun1staff115:00:00 PM17
246617 Dec 20175:35:00 PMSun1staff225:00:00 PM17
246617 Dec 20175:40:00 PMSun1staff225:00:00 PM17
246617 Dec 20175:45:00 PMSun1staff225:00:00 PM17
246617 Dec 20175:50:00 PMSun1staff445:00:00 PM17
246617 Dec 20175:55:00 PMSun1staff115:00:00 PM17

 

 

Message 4 of 7
974 Views
0
duggy
Advocate II
Advocate II
In response to v-yulgu-msft

‎02-05-2019 07:31 AM

Hi,

 

Here is the table broken down:

storeideventdateEventfromTimedaynamestafftypeCustomer VolumeCustomer to Staff RatioEventfromTime (bins)Hour of Day
246617 Dec 20174:00:00 PMSun1staff224:00:00 PM16
246617 Dec 20174:05:00 PMSun1staff224:00:00 PM16
246617 Dec 20174:10:00 PMSun1staff114:00:00 PM16
246617 Dec 20174:15:00 PMSun1staff114:00:00 PM16
246617 Dec 20174:20:00 PMSun1staff224:00:00 PM16
246617 Dec 20174:25:00 PMSun1staff114:00:00 PM16
246617 Dec 20174:30:00 PMSun1staff114:00:00 PM16
246617 Dec 20174:35:00 PMSun1staff114:00:00 PM16
246617 Dec 20174:40:00 PMSun1staff114:00:00 PM16
246617 Dec 20174:45:00 PMSun1staff004:00:00 PM16
246617 Dec 20174:50:00 PMSun1staff004:00:00 PM16
246617 Dec 20174:55:00 PMSun1staff004:00:00 PM16
246617 Dec 20175:00:00 PMSun1staff115:00:00 PM17
246617 Dec 20175:05:00 PMSun1staff115:00:00 PM17
246617 Dec 20175:10:00 PMSun1staff225:00:00 PM17
246617 Dec 20175:15:00 PMSun1staff115:00:00 PM17
246617 Dec 20175:20:00 PMSun1staff115:00:00 PM17
246617 Dec 20175:25:00 PMSun1staff225:00:00 PM17
246617 Dec 20175:30:00 PMSun1staff115:00:00 PM17
246617 Dec 20175:35:00 PMSun1staff225:00:00 PM17
246617 Dec 20175:40:00 PMSun1staff225:00:00 PM17
246617 Dec 20175:45:00 PMSun1staff225:00:00 PM17
246617 Dec 20175:50:00 PMSun1staff445:00:00 PM17
246617 Dec 20175:55:00 PMSun1staff115:00:00 PM17
Message 3 of 7
974 Views
0

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel1

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

View All