Forum Discussion
Average for Unique IDs per category and Total
Hi Community,
I am trying to create a measure that will calculate average for each unique case per category and for total, for the unpivoted table that has one row per category for each case (one case has 5 rows, but only some of those rows are applicable).
I would appreciate any guidance to how to achieve this. Currently I have this formula active:
Below is unpivoted Raw data.
Applicable row needs to be used for category based calculation.
| Case ID | Category | Applicable Row | CSAT |
| 1 | Wine | 1 | 7 |
| 1 | Beer | 1 | 7 |
| 1 | Spirit | 0 | 7 |
| 1 | Juice | 0 | 7 |
| 1 | Water | 0 | 7 |
| 2 | Wine | 0 | 9 |
| 2 | Beer | 0 | 9 |
| 2 | Spirit | 1 | 9 |
| 2 | Juice | 0 | 9 |
| 2 | Water | 0 | 9 |
| 3 | Wine | 1 | 3 |
| 3 | Beer | 1 | 3 |
| 3 | Spirit | 1 | 3 |
| 3 | Juice | 1 | 3 |
| 3 | Water | 1 | 3 |
| 4 | Wine | 1 | 5 |
| 4 | Beer | 0 | 5 |
| 4 | Spirit | 0 | 5 |
| 4 | Juice | 0 | 5 |
| 4 | Water | 1 | 5 |
| 5 | Wine | 1 | 10 |
| 5 | Beer | 0 | 10 |
| 5 | Spirit | 0 | 10 |
| 5 | Juice | 1 | 10 |
| 5 | Water | 0 | 10 |
Here is the output i need. I am able to get either correct calcualtion for category or for total, but not both ion the same visual (Matrix/table).
| Category | avg CSAT |
| Beer | 5 |
| Juice | 6.5 |
| Spirit | 6 |
| Water | 4 |
| Wine | 6.25 |
| Total | 6.8 |
3 Replies
- amitchandak
Super User
Anonymous , Try like
AVERAGEX(Values(table[Case ID]),calculate(AVERAGE(table[CSAT])))
Hey amitchandak , thanks for the reply.
Unfortunatelly, it didnt work.Got total in all rows (as below).
Category avg CSAT Beer 6.8 Juice 6.8 Spirit 6.8 Water 6.8 Wine 6.8 Total 6.8
