Variable Table and measure calculation

[Sudharsanan]

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.

[DOLEARY85]

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 👍

[Sudharsanan]

Thanks @DOLEARY85 for quick response,

but i am not getting the answer as i expected.

[DOLEARY85]

Do you have any more info - can you share what you're seeing?

[Sudharsanan]

@DOLEARY85 i am getting a value of 73.65, but i need to get 76.94296

[DOLEARY85 · Accepted solution]

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 👍