Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago

KPI Visual target as average

I have a KPI visual for which the Value shows accurately as the last period of the year but the Target shows the same, where I want to show the average of that year's values..   See below       ...
Need Help
Tips and Tricks
Anonymous's avatar
Anonymous
Not applicable
1 year ago

 

see below, thank you

 

 

 ValueTarget
Jan 24   20,660,704   23,968,673
Feb 24   21,788,842   23,968,673
Mar 24   23,213,945   23,968,673
Apr 24   22,636,504   23,968,673
May 24   22,723,961   23,968,673
Jun 24   25,621,215   23,968,673
Jul 24   25,341,145   23,968,673
Aug 24   23,048,609   23,968,673
Sep 24   25,639,396   23,968,673
Oct 24   24,680,700   23,968,673
Nov 24   26,198,004   23,968,673
Dec   26,071,045   23,968,673
Average   23,968,673 

 

 

Payroll Expense Brand_Avg =
  VAR MonthlyTotals =
    SUMMARIZE(
        'Main Data',
        'Main Data'[Brand],
        'Main Data'[Year],
        'Main Data'[Month], -- 'Month' represents unique months
        "MonthlyTotal", SUM('Main Data'[Payroll])
    )

  VAR TotalSum = SUMX(MonthlyTotals, [MonthlyTotal])
  VAR MonthCount = DISTINCTCOUNT('Main Data'[Month]) -- Count unique months

RETURN
IF(
    MonthCount > 0,
    TotalSum / MonthCount,
    BLANK()
)
wini_R's avatar
wini_R
Solution Supplier
1 year ago

Hi Anonymous,

I'm not sure about the exact formula in your scenario because sample data does not match the logic in your formula but my best guess would be a calculation similar to that one:

Avg = 
CALCULATE(
    AVERAGEX(
        ADDCOLUMNS(
            SUMMARIZE(tbl2, tbl2[Period]),
            "@sum", CALCULATE([Value Sum])
        ),
        [@sum]
    ),
    ALL(tbl2[Period])
)
  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Thank you, it might be my data configuration, I get the same old results when using your formula

    • Anonymous's avatar
      Anonymous
      Not applicable
      1 year ago

      Thanks for the reply from wini_R.

       

      Hi Anonymous ,

       

      Based on my test, the formula provided by wini_R is functional. Please replace the Period field in the formula with the field you are using. If there are other columns in the visual, please also add that field in the ALL function.

       

       

       

      Best Regards,
      Zhu

       

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