Forum Discussion
Average and sum Dax
Hi All,
I have issues with to get the Divide and Sum Function. Please look into the sample model below
Table
Mat code Sales
12 a1 10
12 a1 20
12 a1 30
12 b1 40
12 b1 50
12 b1 50
13 c1 70
13 c1 80
13 d1 90
13 d1 100
Result Table
Mat Sales
12 100 Logic :(10+20+30+40+50+50)/2( distinct count of "Code", i.e. a1 & b1)
13 170
Total 270
i tried to do sum of averages here, but getting a different result in total
Hope im making sense here
Thanks in advance !
Vinay
Hi,
Write these measures:
Total Sales = SUM(Data[Sales])
Number of codes = DISTINCTCOUNT(Data[code])
Measure = SUMX(VALUES(Data[Mat]),[Total sales]/[Number of codes])
Hope this helps.
7 Replies
- Ashish_Mathur
Super User
Hi,
Write these measures:
Total Sales = SUM(Data[Sales])
Number of codes = DISTINCTCOUNT(Data[code])
Measure = SUMX(VALUES(Data[Mat]),[Total sales]/[Number of codes])
Hope this helps.
- Ashish_Mathur
You are welcome. If my reply helped, please mark it as Answer.
mauriciosoteroResolver III
Hi,
I am not sure if I understand. Can you post the result you want?
v-joesh-msftSolution Sage
Hi Anonymous ,
You can try the measure below:
Measure = VAR dis_code = CALCULATE ( DISTINCTCOUNT ( 'Table'[ code] ) ) RETURN SUMX ( VALUES ( 'Table'[Mat] ), DIVIDE ( SUM ( 'Table'[Sales] ), dis_code ) )Results are as follows:
Best Regards,
Community Support Team _ Joey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.v-joesh-msft Thanks !
Its works fine here in sample model, but not working in my model where in i have 20K Mat & Codes
I'm not understanding where have i gone wrongRegards,
Vinay
- v-joesh-msft
Hi Anonymous ,
Sorry, I made a mistake, you need to create two measures:
aver = DIVIDE(SUM('Table'[Sales]),DISTINCTCOUNT('Table'[ code]))dis_code = SUMX(VALUES('Table'[Mat]),[aver])In fact, this looks like a measure totals problem. Very common. See this post about it
https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376
Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907
Here is a demo, please try it：
Best Regards,
Community Support Team _ Joey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.