sum and average

[rulloa]

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?

[Greg_Deckler · Accepted solution]

https://community.powerbi.com/t5/Community-Blog/Design-Pattern-Groups-and-Super-Groups/ba-p/138149

[Anonymous · Accepted solution]

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

[Greg_Deckler · Accepted solution]

https://community.powerbi.com/t5/Community-Blog/Design-Pattern-Groups-and-Super-Groups/ba-p/138149