Forum Discussion
Getting the Z score for each category between two dates
Hello,
I am new to powerBi and I would like to know if the following is possible.
I have a slicer to select the timeframe for a visual table. If I select from 1/1/24 to 3/1/24 the table looks like
TABLE 1
| Date | Category | Product | Sales |
| 1/1/24 | A | p1 | 10 |
| 1/1/24 | B | p2 | 2 |
| 2/1/24 | A | p3 | 4 |
| 3/1/24 | A | p1 | 8 |
| 3/1/24 | B | p2 | 22 |
| 3/1/24 | B | p4 | 10 |
Now, when I group by Date and Category, I get
TABLE 2
| Date | Category | Total Sales |
| 1/1/24 | A | 10 |
| 2/1/24 | A | 4 |
| 3/1/24 | A | 8 |
| 1/1/24 | B | 2 |
| 3/1/24 | B | 32 |
From this information, I would like to compare the sales with each category with itself and get the Z-score on each day.
Calculations:
The average sales for Category A from 1/1/24 to 3/1/24 is ( 10 + 4 +8 ) / 3 = 7.3
The average sales for Category B from 1/1/24 to 3/1/24 is ( 2 + 0 + 32 ) / 3 = 11.3
Using these values we can work out the standard deviations of A and B: std_A = 2.5 ; std_B = 14.6
So the Z score for A for each day is
TABLE 3
| Date | Z Score |
| 1/1/24 | 1.08 |
| 2/1/24 | 1.32 |
| 3/1/24 | 0.28 |
Repeat the same calculation for B, and its maximum is 1.47.
The final table or result I want to achieve is to rank the Categories by their highest Z score, that is
TABLE 4
| Category | Max Z Score |
| B | 1.47 |
| A | 1.32 |
Thank you very much.
2 Replies
Hi neln ,
1. Create a calculation table to get a table with 0 data and calculate the standard deviation.
123 = var _table1= SUMMARIZE('Table 2'. "Date", DATE(2024,2,1), "Category", "B". "Total Sales",0) var _table2= UNION( 'Table 2',_table1) var _table2= UNION( 'Table 2',_table1) ADDCOLUMNS( _table2, "Result", STDEVX.P(FILTER(_table2,[Category] = EARLIER([Category])),[Total Sales]))2. Create a calculated column to get the z-score.
z-score = VAR _ave = AVERAGEX(FILTER('123','123'[Category] = EARLIER('123'[Category])), '123'[Total Sales]) RETURN ABS((('123'[Total Sales] - _ave) / '123'[Result]))3. Create a calculation table to get the final result.
Table 5 = SUMMARIZE('123','123'[Category], "max z-score",MAX('123'[z-score]))If your Current Period does not refer to this, please clarify in a follow-up reply.
Best Regards,
Clara Gong
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi Anonymous ,
Thanks for your reply.
I made a mistake in Table 3 as the z scores can't be all positive but this is an easy fix by removing ABS().Though my request is slightly different. The calculation should be gerenated dynamically based on the Calendar slider. I have a separate Calendar table linked to Table 1. When a user selects a specific date range I would like to automatically calculate the z score for each category in that period and return the max value for each.
Therefore, I think the modification is needed for var _table1 since it is hard coded in your example:
var _table1= SUMMARIZE('Table 2'. "Date", DATE(2024,2,1), "Category", "B". "Total Sales",0)Is there way to fill in the 0s for all categories dynamically on the selected range of dates without sales?
Many thanks,
N
