Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
duggy
Advocate II
Advocate II

Establishing if event is higher than upper quartile for the store for the period

Hi,

 

I have the table below with the total number of events per store per day. I need to establish the upper quartile for the given store for the period and then if the event at that time is higher than the upper quartile. If it is then place a 1 in the column.

 

The idea being if the events happen in the higher regions (higher than the upper quartile) then I need to identify by hour which hours had the highest unnatural events.

 

How would I work out the upper quartile for the store per day and then check if the row events are higher than this upper quartile and place a 1 in the new column (therefore adding the new column I can order by highest hour).

 

Make sense?

storeidDateTimeEvents
10586Friday, 01 February 20199:00:00 AM5
10586Friday, 01 February 201910:00:00 AM1
10886Friday, 01 February 201910:00:00 AM4
8118Friday, 01 February 201910:00:00 AM2
10586Friday, 01 February 201911:00:00 AM6
10886Friday, 01 February 201911:00:00 AM6
8118Friday, 01 February 201911:00:00 AM4
10586Friday, 01 February 201912:00:00 PM3
8118Friday, 01 February 201912:00:00 PM5
10886Friday, 01 February 201912:00:00 PM4
8118Friday, 01 February 20191:00:00 PM3
10886Friday, 01 February 20191:00:00 PM4
10586Friday, 01 February 20191:00:00 PM8
10586Friday, 01 February 20192:00:00 PM6
8118Friday, 01 February 20192:00:00 PM3
10886Friday, 01 February 20192:00:00 PM5
10586Friday, 01 February 20193:00:00 PM10
10886Friday, 01 February 20193:00:00 PM3
10586Friday, 01 February 20194:00:00 PM12
8118Friday, 01 February 20194:00:00 PM1
10886Friday, 01 February 20194:00:00 PM0
8118Friday, 01 February 20195:00:00 PM3
10586Friday, 01 February 20195:00:00 PM0
10886Friday, 01 February 20195:00:00 PM7
8118Friday, 01 February 20196:00:00 PM0
10586Friday, 01 February 20196:00:00 PM2
8118Friday, 01 June 20189:00:00 AM1
10586Friday, 01 June 20189:00:00 AM1
10586Friday, 01 June 201810:00:00 AM3
8118Friday, 01 June 201810:00:00 AM5
10586Friday, 01 June 201811:00:00 AM5
8118Friday, 01 June 201811:00:00 AM4
8118Friday, 01 June 201812:00:00 PM1
10586Friday, 01 June 201812:00:00 PM1
10586Friday, 01 June 20181:00:00 PM5
8118Friday, 01 June 20181:00:00 PM6
10586Friday, 01 June 20182:00:00 PM1
8118Friday, 01 June 20182:00:00 PM8
10586Friday, 01 June 20183:00:00 PM2
8118Friday, 01 June 20183:00:00 PM8
8118Friday, 01 June 20184:00:00 PM5
10586Friday, 01 June 20184:00:00 PM2
8118Friday, 01 June 20185:00:00 PM9
10586Friday, 01 June 20185:00:00 PM0
8118Friday, 01 June 20186:00:00 PM3
8118Friday, 01 March 20198:00:00 AM1
10886Friday, 01 March 20198:00:00 AM1
10586Friday, 01 March 20199:00:00 AM2
8118Friday, 01 March 20199:00:00 AM7
10886Friday, 01 March 20199:00:00 AM7
8118Friday, 01 March 201910:00:00 AM2
10586Friday, 01 March 201910:00:00 AM-3
10886Friday, 01 March 201910:00:00 AM7
10586Friday, 01 March 201911:00:00 AM3
10886Friday, 01 March 201911:00:00 AM7
8118Friday, 01 March 201911:00:00 AM2
10586Friday, 01 March 201912:00:00 PM1
8118Friday, 01 March 201912:00:00 PM4
10886Friday, 01 March 201912:00:00 PM4
10586Friday, 01 March 20191:00:00 PM0
10886Friday, 01 March 20191:00:00 PM2
8118Friday, 01 March 20191:00:00 PM6
10586Friday, 01 March 20192:00:00 PM1
8118Friday, 01 March 20192:00:00 PM4
10886Friday, 01 March 20192:00:00 PM9
1 ACCEPTED SOLUTION
AlB
Super User
Super User

Hi @duggy,

 

Try this for your new calculated column. Make sure Table1[Events] is of Decimal number type and not Whole number.

 

 

NewColumn =
VAR _Perc =
    CALCULATE (
        PERCENTILE.INC ( Table1[Events]; 75/100 );
        ALLEXCEPT ( Table1; Table1[Date]; Table1[storeid] )
    )
RETURN
    IF ( Table1[Events] > _Perc; 1 )

View solution in original post

2 REPLIES 2
AlB
Super User
Super User

Hi @duggy,

 

Try this for your new calculated column. Make sure Table1[Events] is of Decimal number type and not Whole number.

 

 

NewColumn =
VAR _Perc =
    CALCULATE (
        PERCENTILE.INC ( Table1[Events]; 75/100 );
        ALLEXCEPT ( Table1; Table1[Date]; Table1[storeid] )
    )
RETURN
    IF ( Table1[Events] > _Perc; 1 )

Awesome, works. Thank you!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.