How to perform Group by Average opreation using DAX in power BI?

1
votes

I have a sample dataset as below in power BI.

enter image description here

I want to first group by DeliveryMonth and sum the column Item and then group by Period and take average of column Item. If I directly use average in aggregation it is getting average of whole column. instead I just want average of filtered grouped data by category. my desired output should be as shown below.

enter image description here

Current Average aggregation shows this in power bi. enter image description here

I have tried creating DAX measure using different formulas but did not quite get it. Is there anyway I can get this working? I would really appreciate any help on this. Thanks.

1
powerbidaxpowerbi-desktop

1 Answers

0
votes

I have replicated your model, the following calculation should work. In essence, is grouping the values using SUMMARIZE under the desired granularity. I couldn't match the number 4391.7, I don't know if that's an error.

Calculation: Measure

Avg. Item Sum = 
    AVERAGEX(
        SUMMARIZE('Table','Table'[Category],'Table'[DeliveryMonth],'Table'[Period]),
        CALCULATE(sum('Table'[Item]))
        )

Output

Category Period Avg. Item Sum
A Sum20 1497.23
B Sum20 525.1
A Fal20 1473.1
B Fal20 440.9
A Win20 1102.2
B Win20 1116.3
A Spr21 780.3
B Spr21 567.1

For future reference, the original table as markdown

Category DeliveryMonth Item Period
A 06 January 2020 963.9 Sum20
A 06 January 2020 500 Sum20
A 06 January 2020 100 Sum20
A 07 January 2020 963.9 Sum20
A 07 January 2020 400 Sum20
A 07 January 2020 200 Sum20
A 08 January 2020 963.9 Sum20
A 08 January 2020 100 Sum20
A 08 January 2020 300 Sum20
A 09 January 2020 1473.1 Fal20
A 10 January 2020 1473.1 Fal20
A 11 January 2020 1473.1 Fal20
A 12 January 2020 1102.2 Win20
A 01 January 2021 1102.2 Win20
A 02 January 2021 1102.2 Win20
A 03 January 2021 780.3 Spr21
A 04 January 2021 780.3 Spr21
A 05 January 2021 780.3 Spr21
B 06 January 2020 525.1 Sum20
B 07 January 2020 525.1 Sum20
B 08 January 2020 525.1 Sum20
B 09 January 2020 440.9 Fal20
B 10 January 2020 440.9 Fal20
B 11 January 2020 440.9 Fal20
B 12 January 2020 1116.3 Win20
B 01 January 2021 1116.3 Win20
B 02 January 2021 1116.3 Win20
B 03 January 2021 567.1 Spr21