Compare Total per month vs average per month per year

[brumero]

Hi, 

I'm trying to generate a report in Power BI, showing in a chart the total expenditures per month against the average per month per year of the same measure. I have done this report before in Microstrategy, now I want to have the same in Power BI.

This should be the final chart. To have in mind, there is a slicer in the report, selecting certain years. 

[amitchandak]

@brumero ,

try like

Averagex(Summarize(Filter(Table,Table[Year]=max(Table[year])),Table[Month],table[Year],"_sum",sum(Table[Value])),[_sum])

[brumero]

Thank you @amitchandak , but unfortunately I got the same result:

[Greg_Deckler · Accepted solution]

Sorry, @brumero - my mistake, I meant:

Average Measure = 
  VAR __Year = MAX('Table'[Year])
  VAR __Table = 
      SUMMARIZE(
        FILTER(ALL('Table'),[Year] = __Year),
        [Month],
        "__Value",SUM('Table'[Value])
      )
RETURN
  AVERAGEX(__Table,[__Value])

It's the littlest things...

[brumero]

Thank you @Greg_Deckler.

Works perfectly

[Greg_Deckler]

Sorry about that, I knew I needed an all in there but apparently on my first go just forgot to include it!! Glad we got there!

[Greg_Deckler]

So the average would be something like:

Average Measure = 
  VAR __Year = MAX('Table'[Year])
  VAR __Table = 
      SUMMARIZE(
        FILTER('Table',[Year] = __Year),
        [Month],
        "__Value",SUM('Table'[Value])
      )
RETURN
  AVERAGEX(__Table,[__Value])

Use a column/line chart. Your final chart will look a little different than that in Power BI.

[brumero]

Thanks @Greg_Deckler ,

I'd tried but I get the same result as other methods. Let me show you.

$ Prom Año = 
  VAR __Year = MAX('01 Fecha'[Fecha].[Year])
  VAR __Table = 
      SUMMARIZE(
        FILTER('01 Fecha',[Year] = __Year),
        [Month],
        "__Value",SUM('05 Mayor PA'[Gasto U$D])
      )
RETURN
  AVERAGEX(__Table,[__Value])