Forum Discussion

Seyi38's avatar
Seyi38
Regular Visitor
2 years ago
Solved

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...
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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's avatar
Seyi38
Regular Visitor
2 years ago

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?