Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
This is how my data set looks like (it has some duplication but very hard to delete -- so I want to try 'calculated average'
Like the following:
The real average should be 10+5+20
but if I use the category average I belive it is:
(10*2+5*3+20) /(4A+3B+1C)
if no sales, then product should not be count
Product A Sales
A 10
A 10
A null
A null
B 5
B 5
B 5
C 20
Solved! Go to Solution.
hi, @baron33
what you want (The real average should be 10+5+20) may be a measure totals problem. 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
For your case, just try this formula
Measure = SUMX(VALUES('Table'[Product]),CALCULATE(AVERAGE('Table'[Sales])))
or JosefPrakljacic's solution may meet your requirements.
here is sample pbix file, please try it.
Regards,
Lin
Hi,
What result do you want to see = 10+5+20=35 or 35/3=11.67?
This haha 10+5+20=35
Hi,
Drag this measure to a card visual
=SUMX(VALUES(Data[Product]),MIN(Data[Sales]))
Hope this helps.
hi, @baron33
what you want (The real average should be 10+5+20) may be a measure totals problem. 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
For your case, just try this formula
Measure = SUMX(VALUES('Table'[Product]),CALCULATE(AVERAGE('Table'[Sales])))
or JosefPrakljacic's solution may meet your requirements.
here is sample pbix file, please try it.
Regards,
Lin
Hi @baron33 ,
does this help you? (I'm not quite sure if I have understood you correctly)
Average Disinct = AVERAGEX(DISTINCT('Table'[Sales]),'Table'[Sales])
If this post was helpful may I ask you to mark it as solution and give it a 'thumbs up'? This will also help others
Have a nice day!
BR,
Josef
Graz - Austria
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
100 | |
78 | |
64 | |
58 |
User | Count |
---|---|
148 | |
113 | |
97 | |
84 | |
67 |