Average chart values based on on-page filters

[yong9lum]

I want my column chart to be able to show average values based on the period that I filter, eg. if I multi-select JANUARY 2020 & FEBRUARY 2020, the chart will show JANUARY 2020 + FEBRUARY 2020 values/2. Show value as percent of grand total doesn't work for me because I will need the raw values to be displayed on the chart.

Anyone know how I can do this?

[v-janeyg-msft · Accepted solution]

Hi, @yong9lum 

It’s my pleasure to answer for you.

According to your description,You can create a measure to calculate the desired result.

Like this:

avg = 
VAR a =
    CALCULATE ( SUM ( 'Table'[value] ), ALLSELECTED ( 'Table' ) )
VAR b =
    CALCULATE ( DISTINCTCOUNT ( 'Table'[Period] ), ALLSELECTED ( 'Table' ) )
RETURN
    DIVIDE ( a, b )

Here is my sample .pbix file.Hope it helps.

If it doesn’t solve your problem, please feel free to ask me.

Best Regards

Janey Guo

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@yong9lum , with help from a date table and months to be selected from Slicer , Try a measure like the example

Avg MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date])) /CALCULATE(Distinctcount('Date'[Date]),DATESMTD('Date'[Date]), not(isblank(Sales[Sales Amount])))

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 :radacad sqlbi My Video Series Appreciate your Kudos.

[Anonymous]

Hi @yong9lum 

This is easy, just create below measures.

Measure 1= sum(value)

Measure 2= Averagex(values(Period),Measure 1

This will work fine, please accept this as solution if this works fine with you.