Dynamically calculate average each month

[Anonymous]

Hi everyone,

I have a DAX question; I have a table of sales values along with each month as shown below:

I also have an order table as shown:

And those two tables are hooked up as follows:

I am trying to have a table where on the rows, I will get the months and the values and the average value something like that:

The caveat is that I am displaying only the first 4months, that is January to April using a visual level filter.

As you can see from the table above, the measure
(1) "Average" is returning the average over 12 months

(2) "Average1" is returning the right answer

(3) "Average2" is not working

Here is the DAX for each one:

Average = CALCULATE(AVERAGE(Sales[Sales]),all('Order'[Month],'Order'[Order]))

Average1 = CALCULATE(AVERAGE(Sales[Sales]),filter(all('Order'[Month],'Order'[Order]),'Order'[Month] IN {"January","February","March","April"}))

Average2 = CALCULATE(AVERAGE(Sales[Sales]),filter(all('Order'[Month],'Order'[Order]),'Order'[Month] IN VALUES('Order'[Month])))

What I was trying to do with "Average 2" was to get the same expression as "Average1" to update automatically when I include or exclude other months from my visual level filter.

Is there anyway to do that?

[amitchandak · Accepted solution]

@Anonymous , In such case please prefer to have date table in such case , try like

Average2 = CALCULATE(AVERAGE(Sales[Sales]),filter(all('Order'),'Order'[Month] IN VALUES('Order'[Month])))

or

Average2 = CALCULATE(AVERAGE(Sales[Sales]),filter(all('Date'),'Date'[Month] IN VALUES('Date'[Month])))

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-YTD-LYTD-Week-Over-Week/m-p/1051626#M184


Appreciate your Kudos.

[jdbuchanan71]

@Anonymous 

Give this a try, 2 measures.

Sales Amount = SUM (Sales[Sales])

Average Sales =
CALCULATE (
    AVERAGEX ( ALLSELECTED ( 'Order'[Month] ), [Sales Amount] ),
    ALLSELECTED ( 'Order' )
)