Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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?
storeid | Date | Time | Events |
10586 | Friday, 01 February 2019 | 9:00:00 AM | 5 |
10586 | Friday, 01 February 2019 | 10:00:00 AM | 1 |
10886 | Friday, 01 February 2019 | 10:00:00 AM | 4 |
8118 | Friday, 01 February 2019 | 10:00:00 AM | 2 |
10586 | Friday, 01 February 2019 | 11:00:00 AM | 6 |
10886 | Friday, 01 February 2019 | 11:00:00 AM | 6 |
8118 | Friday, 01 February 2019 | 11:00:00 AM | 4 |
10586 | Friday, 01 February 2019 | 12:00:00 PM | 3 |
8118 | Friday, 01 February 2019 | 12:00:00 PM | 5 |
10886 | Friday, 01 February 2019 | 12:00:00 PM | 4 |
8118 | Friday, 01 February 2019 | 1:00:00 PM | 3 |
10886 | Friday, 01 February 2019 | 1:00:00 PM | 4 |
10586 | Friday, 01 February 2019 | 1:00:00 PM | 8 |
10586 | Friday, 01 February 2019 | 2:00:00 PM | 6 |
8118 | Friday, 01 February 2019 | 2:00:00 PM | 3 |
10886 | Friday, 01 February 2019 | 2:00:00 PM | 5 |
10586 | Friday, 01 February 2019 | 3:00:00 PM | 10 |
10886 | Friday, 01 February 2019 | 3:00:00 PM | 3 |
10586 | Friday, 01 February 2019 | 4:00:00 PM | 12 |
8118 | Friday, 01 February 2019 | 4:00:00 PM | 1 |
10886 | Friday, 01 February 2019 | 4:00:00 PM | 0 |
8118 | Friday, 01 February 2019 | 5:00:00 PM | 3 |
10586 | Friday, 01 February 2019 | 5:00:00 PM | 0 |
10886 | Friday, 01 February 2019 | 5:00:00 PM | 7 |
8118 | Friday, 01 February 2019 | 6:00:00 PM | 0 |
10586 | Friday, 01 February 2019 | 6:00:00 PM | 2 |
8118 | Friday, 01 June 2018 | 9:00:00 AM | 1 |
10586 | Friday, 01 June 2018 | 9:00:00 AM | 1 |
10586 | Friday, 01 June 2018 | 10:00:00 AM | 3 |
8118 | Friday, 01 June 2018 | 10:00:00 AM | 5 |
10586 | Friday, 01 June 2018 | 11:00:00 AM | 5 |
8118 | Friday, 01 June 2018 | 11:00:00 AM | 4 |
8118 | Friday, 01 June 2018 | 12:00:00 PM | 1 |
10586 | Friday, 01 June 2018 | 12:00:00 PM | 1 |
10586 | Friday, 01 June 2018 | 1:00:00 PM | 5 |
8118 | Friday, 01 June 2018 | 1:00:00 PM | 6 |
10586 | Friday, 01 June 2018 | 2:00:00 PM | 1 |
8118 | Friday, 01 June 2018 | 2:00:00 PM | 8 |
10586 | Friday, 01 June 2018 | 3:00:00 PM | 2 |
8118 | Friday, 01 June 2018 | 3:00:00 PM | 8 |
8118 | Friday, 01 June 2018 | 4:00:00 PM | 5 |
10586 | Friday, 01 June 2018 | 4:00:00 PM | 2 |
8118 | Friday, 01 June 2018 | 5:00:00 PM | 9 |
10586 | Friday, 01 June 2018 | 5:00:00 PM | 0 |
8118 | Friday, 01 June 2018 | 6:00:00 PM | 3 |
8118 | Friday, 01 March 2019 | 8:00:00 AM | 1 |
10886 | Friday, 01 March 2019 | 8:00:00 AM | 1 |
10586 | Friday, 01 March 2019 | 9:00:00 AM | 2 |
8118 | Friday, 01 March 2019 | 9:00:00 AM | 7 |
10886 | Friday, 01 March 2019 | 9:00:00 AM | 7 |
8118 | Friday, 01 March 2019 | 10:00:00 AM | 2 |
10586 | Friday, 01 March 2019 | 10:00:00 AM | -3 |
10886 | Friday, 01 March 2019 | 10:00:00 AM | 7 |
10586 | Friday, 01 March 2019 | 11:00:00 AM | 3 |
10886 | Friday, 01 March 2019 | 11:00:00 AM | 7 |
8118 | Friday, 01 March 2019 | 11:00:00 AM | 2 |
10586 | Friday, 01 March 2019 | 12:00:00 PM | 1 |
8118 | Friday, 01 March 2019 | 12:00:00 PM | 4 |
10886 | Friday, 01 March 2019 | 12:00:00 PM | 4 |
10586 | Friday, 01 March 2019 | 1:00:00 PM | 0 |
10886 | Friday, 01 March 2019 | 1:00:00 PM | 2 |
8118 | Friday, 01 March 2019 | 1:00:00 PM | 6 |
10586 | Friday, 01 March 2019 | 2:00:00 PM | 1 |
8118 | Friday, 01 March 2019 | 2:00:00 PM | 4 |
10886 | Friday, 01 March 2019 | 2:00:00 PM | 9 |
Solved! Go to Solution.
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 )
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!
User | Count |
---|---|
141 | |
113 | |
104 | |
78 | |
64 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |