DAX PowerBI SUM of Averages with user input

Question

I'm new in DAX and I need your help please. Here is my problem: I need to calculate the sum of the average sales of the products for the years selected by the user. Let me explain, here is my source:

Table1

Product Year    SalesAM SalesPM State
Pants   2015    6477    5787    California
Pants   2015    9675    7967    NewYork
Pants   2016    4577    5785    California
Pants   2016    6467    9357    NewYork
Pants   2017    2524    9679    California
Pants   2017    5745    4674    NewYork
Pants   2018    3532    6585    California
Pants   2018    6746    9575    NewYork
Shirts  2015    3452    9577    California
Shirts  2015    3464    6896    NewYork
Shirts  2016    2452    5785    California
Shirts  2016    5685    9679    NewYork
Shirts  2017    3635    4674    California
Shirts  2017    3435    9457    NewYork
Shirts  2018    5745    5688    California
Shirts  2018    2355    7585    NewYork
Shoes   2015    6653    9657    California
Shoes   2015    6766    9678    NewYork
Shoes   2016    5684    5788    California
Shoes   2016    5745    9577    NewYork
Shoes   2017    6887    7976    California
Shoes   2017    2454    4746    NewYork
Shoes   2018    4674    3567    California
Shoes   2018    6744    9686    NewYork

The user chooses from 2016 to 2018:

Year selection

For each state, I need average sales by product according to the years selected by the user, then sum these averages by product. Here is what I reproduced in Excel:

Average sales by product and by state according to the selected years

I managed to average with the Visualization tab but it calculates the average of the averages and I need the sum of the averages (Moyenne=Average):

PowerBI Average of Average

Does anyone have any ideas of how to do it in DAX?

Thanks and regards,

Jon Jon · Accepted Answer · 2019-09-27T15:29:12

You'll need to break this up into a measures, rather than use a visual. Replace 'Table' with your table name. The one for Sales AM is below. First create the AVERAGE measure

AVG AM = AVERAGE('Table'[SalesAM])

Which is then used below

Measure = CALCULATE(SUMX(SUMMARIZE('Table', 'Table'[product], 'Table'[State],"avg",[AVG AM]), [avg]), ALLEXCEPT('Table', 'Table'[Year], 'Table'[Product]))

I've broken it down into the parts

SUMMARIZE('Table', 'Table'[product], 'Table'[State],"avg", [AVG AM], [avg])

This create a table that works out the average. You then use this table to be used in the calculation of the SUMX

CALCULATE(SUMX(

Then the next section defines the filter context that it will apply to, so you can select and break it down only by product and year.

ALLEXCEPT('Table', 'Table'[Year], 'Table'[Product])

You can do it all in one measure if you wish to.

CALCULATE(SUMX(SUMMARIZE('Table', 'Table'[product], 'Table'[State], "avg", AVERAGE('Table'[SalesAM])), [avg]), ALLEXCEPT('Table', 'Table'[Year], 'Table'[Product]))

If you are new to DAX, try the book Power Pivot and Power Bi: The Excel User's Guide to Dax, Power Query, Power Bi & Power Pivot in Excel 2010-2016 by Rob Collie, it gives a good intro to DAX

DAX PowerBI SUM of Averages with user input

1 Answers