cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Regular Visitor

## How to Calculate Monthly Average for KPI Visual Target

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.

1 ACCEPTED SOLUTION
Community Support

Hi @Seyi38 ,

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

The Table data is shown below:

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.

4 REPLIES 4
Community Support

Hi @Seyi38 ,

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

The Table data is shown below:

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.

Regular Visitor

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

Regular Visitor

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?

Super User

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!

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.