Calculate sum of averages by month and item?

[Jolyon]

Hello,

I have a table with forecast revenue for three Items and three Months.
The problem is, that there can be several forecast records for each Month (for Example: Item 1 has 4 forecasts for July),as in source table below.

Now I need to calculate the sum of average revenue of all Items by Months (the revenue should be taken as average for each Month and Item).

And below is just sample table to understand, what data I need in the result  and the line diagramm.

How can I first calculate the average value by Month and Item?

and then calculate the sum of averages?

It it possible to do it in one formula?

I tried a lot of things with the little DAX i know but nothing worked out correctly. 

thank you in advance!

[AlexChen]

Hi,

You can create a table using one formula to calculate average  value by month and item:

avg foreast = SUMMARIZE(

                foreast,

                foreast[product],

                foreast[month],

                "Average foreast revenue(by month)", AVERAGEX(foreast, foreast[foreast Revenue])

                )

Best Regards

Alex

[ankitpatira · Accepted solution]

@Jolyon It's actually very easy to do. After importing data go to power bi desktop query editor and under Transform tab Group By as below.