Hi all,
I am trying to calculate the standard deviaton of the amount of sales for each product within a certain product category, in order to eventually calculate a Z-score for each customer. However, some customer do not have sales for a specific product (i.e. amount of sales is blank). See example below
Filter: Product Category = Y
| Customer | Product | Amount of Sales | Average |
| 1 | A | 3 | 2,4 |
| 2 | A | | 2,4 |
| 3 | A | 2 | 2,4 |
| 4 | A | | 2,4 |
| 5 | A | 7 | 2,4 |
| 1 | B | | 1,4 |
| 2 | B | 4 | 1,4 |
| 3 | B | | 1,4 |
| 4 | B | 2 | 1,4 |
| 5 | B | 1 | 1,4 |
When I create substitute the amount of sales measure with the following measure:
Amount of sales incl 0 = IF(ISBLANK(_Measures[Amount of Sales]), 0, _Measures[Amount of Sales])
The result ignores the product category filter and returns 0's for all customers in the customer dimension. See example below for product A
| Customer | Product | Amount of sales incl 0 |
| 1 | A | 3 |
| 2 | A | 0 |
| 3 | A | 2 |
| 4 | A | 0 |
| 5 | A | 7 |
| 6 | A | 0 |
| 7 | A | 0 |
| 8 | A | 0 |
| 9 | A | 0 |
| 10 | A | 0 |
| 11 | A | 0 |
| 12 | A | 0 |
Does anyone have any suggestions on how to formulate my StDev or Amount of Sales formula
Suggestions are more than welcome!
Thank you
Hendrik
