Forum Discussion
Variable Table and measure calculation
Hi All,
I have a case where i need to group a set of data and bring the average.
Below is my dataset and i need to group the products to have the sales and qty value and then i need to Avg.
| Product | Sales | Qty |
| AA | 160.7228 | 2.0000 |
| BB | 81.8864 | 1.0000 |
| AA | 37.7468 | 1.0000 |
| AA | 80.764 | 1.0000 |
| AA | 80.764 | 1.0000 |
Expected grouping
ProductSum of SalesSum of Qty
| AA | 359.9976 | 5 |
| BB | 81.8864 | 1 |
Expected Avg value for both :
| Product | Sum of Sales | Sum of Qty | Avg |
| AA | 359.9976 | 5 | 71.99952 |
| BB | 81.8864 | 1 | 81.8864 |
Final Avg for both the products shoudl be as
76.94296 |
i dont want to create an additional table but wanted to have the above calculations as meaasure.
thanks for you support as always.
Ah okay so the final average needs to be an average of the average measure,
try looking at these:
https://community.powerbi.com/t5/Desktop/Average-of-measure/td-p/50935
https://community.powerbi.com/t5/Desktop/calculate-average-of-calculated-measure/td-p/568198
If I answered your question, please mark my post as solution, Appreciate your Kudos 👍
5 Replies
Hi,
this measure should work for the sum:
Measure = CALCULATE(sum('Table'[Sales]),ALLEXCEPT('Table','Table'[Product]))just create a 2nd one for QTY.For the average:Measure 2 = CALCULATE(AVERAGE('Table'[Sales]),ALLEXCEPT('Table','Table'[Product]))If I answered your question, please mark my post as solution, Appreciate your Kudos 👍Do you have any more info - can you share what you're seeing?
