Forum Discussion
sum and average
Hello everyone, the question is:
I have the following table.
| Customer | Sub Customer | Month | N° | Amount |
| A | AX | 1 | 1000000 | 31 |
| A | AX | 2 | 950000 | 28 |
| A | AX | 3 | 902500 | 31 |
| A | AX | 4 | 857375 | 30 |
| A | AX | 5 | 814506 | 31 |
| A | AX | 6 | 773781 | 30 |
| A | AX | 7 | 735092 | 31 |
| A | AX | 8 | 698337 | 31 |
| A | AX | 9 | 663420 | 30 |
| A | AX | 10 | 630249 | 31 |
| A | AX | 11 | 10000 | 3 |
| A | AY | 1 | 950000 | 31 |
| A | AY | 2 | 902500 | 28 |
| A | AY | 3 | 857375 | 31 |
| A | AY | 4 | 814506 | 30 |
| A | AY | 5 | 773781 | 31 |
| A | AY | 6 | 735092 | 30 |
| A | AY | 7 | 698337 | 31 |
| A | AY | 8 | 663420 | 31 |
| A | AY | 9 | 630249 | 30 |
| A | AY | 10 | 598737 | 31 |
| A | AY | 11 | 568800 | 8 |
| B | BX | 1 | 850000 | 31 |
| B | BX | 2 | 807500 | 28 |
| B | BX | 3 | 767125 | 31 |
| B | BX | 4 | 728769 | 30 |
| B | BX | 5 | 692330 | 31 |
| B | BX | 6 | 657714 | 30 |
| B | BX | 7 | 624828 | 31 |
| B | BX | 8 | 593587 | 31 |
| B | BX | 9 | 563907 | 30 |
| B | BX | 10 | 535712 | 31 |
| B | BX | 11 | 508926 | 15 |
| B | BY | 1 | 700000 | 31 |
| B | BY | 2 | 665000 | 28 |
| B | BY | 3 | 631750 | 31 |
| B | BY | 4 | 600163 | 30 |
| B | BY | 5 | 570154 | 31 |
| B | BY | 6 | 541647 | 30 |
| B | BY | 7 | 514564 | 31 |
| B | BY | 8 | 488836 | 31 |
| B | BY | 9 | 464394 | 30 |
| B | BY | 10 | 441175 | 31 |
| B | BY | 11 | 12 |
I need to calculate in Power BI the amount for each subcustomer which is the sum of the amount (easy) but when grouping by customer should be the average of the amount of the subcustomers in every customer. And before the customer i have more levels. And these leves must be calculated in the same way that the customers like this.
| Customer | Sub Customer | Month | Total Amount |
| A | AX | 307 | |
| AY | 312 | ||
| Total A | avg | 309,5 | |
| B | BX | 319 | |
| BY | 316 | ||
| Total B | avg | 317,5 | |
| Total general | avg | 313,5 |
Could you help me to do it?
Hi rulloa,
Create the following measures in your table and create a Matrix visual as shown in below screenshot.
SumAmountbycustomer = CALCULATE(SUM('Table'[Amount]),ALLEXCEPT('Table','Table'[Customer]))
countofsubcustomer = CALCULATE(DISTINCTCOUNT('Table'[Sub Customer]),ALLEXCEPT('Table','Table'[Customer]))
Average = [SumAmountbycustomer]/[countofsubcustomer]
Thanks,
Lydia Zhang
2 Replies
Hi rulloa,
Create the following measures in your table and create a Matrix visual as shown in below screenshot.
SumAmountbycustomer = CALCULATE(SUM('Table'[Amount]),ALLEXCEPT('Table','Table'[Customer]))
countofsubcustomer = CALCULATE(DISTINCTCOUNT('Table'[Sub Customer]),ALLEXCEPT('Table','Table'[Customer]))
Average = [SumAmountbycustomer]/[countofsubcustomer]
Thanks,
Lydia Zhang