How to Calculate Monthly Average for KPI Visual Target

[Seyi38]

I am trying to make the Target of my KPI visual the average monthly amount. I used this measure "Monthly Amount AVG = CALCULATE (
DIVIDE(Ops[View Value],DISTINCTCOUNT(Date_Dim[MonthYear])), All(Date_Dim[MonthYear])
)" and this works at first but when any Month and Year filter gets applied to the page that is not the most recent month(March 2024 in this case), the KPI value retuans blank even though the monthly average appears correctly. I need help to resolve this. 

[v-zhouwen-msft · Accepted solution]

Hi @samratpbi ,thanks for the quick reply, I'll add further.

Hi @Seyi38 ,

The measure I've created don't appear to be null for the moment.

The Table data is shown below:

Please follow these steps:
1. Use the following DAX expression to create a measure

Monthly Average = 
VAR _a = CALCULATE(COUNTROWS(SUMMARIZE('Table',[Year],[Month])),ALL('Table'))
VAR _b = CALCULATE(SUM('Table'[Value]),ALL('Table'))
RETURN DIVIDE(_b,_a)

2.Final output

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

[Seyi38]

This works. Thank you! Will let you know if any issues come up. I hope it won't though

[Seyi38]

Hi, Thanks for you response. The measure is just a sum of different columns, it does not have any MAX() in it. It's just called "current month value" since the KPI shows the value for the latest date and I don't mind that. I noticed that the KPI visual works fine when I removed this average measure "CALCULATE (
DIVIDE(Ops[View Value],DISTINCTCOUNT(Date_Dim[MonthYear])), All(Date_Dim[MonthYear])
)" from the target. Is there a way to calculate the monthly average without it affecting the KPI? I tried this other measure: "

AVERAGEX(

    VALUES(Dates_Dim[Date]),

    CALCULATE(

        Ops[View Value]

    )

)"

and while selecting previous months does not return blank, it just returns the current month's value instead of the average for all selected months since it doesn't consider the other months and year selected except whatever is the most recent month. Using the second measure, it works fine in a table by returning the average value for all the months highlighted but that's not the case with the KPI visual. Is there a way to get the target to be an average of all months selected while the default behaviour for the KPI value remains?

[samratpbi]

Hi, it seems the measure which you have created for the current month value, have some filter to return the max month value only, which is returning blank when other months selected. If you want to select different months then remove that possible max month filter.

Hope this would resolve your issue.

If this helps to resolve your problem then please mark it as solution, Thanks!