KPI Visual target as average

Question

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

Thank you in advance

wini_r · Answer

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])
)

wini_r · Answer

Hey&nbsp;Anonymous,Can you please paste some sample data in a text format, your current result including formulas as well as the expected result? It would be very helpful to look&nbsp; into the issueHow to Get Your Question Answered Quickly

anonymous · Answer

&nbsp;see below, thank you&nbsp;&nbsp;&nbsp;ValueTargetJan 24&nbsp;&nbsp; 20,660,704&nbsp;&nbsp; 23,968,673Feb 24&nbsp;&nbsp; 21,788,842&nbsp;&nbsp; 23,968,673Mar 24&nbsp;&nbsp; 23,213,945&nbsp;&nbsp; 23,968,673Apr 24&nbsp;&nbsp; 22,636,504&nbsp;&nbsp; 23,968,673May 24&nbsp;&nbsp; 22,723,961&nbsp;&nbsp; 23,968,673Jun 24&nbsp;&nbsp; 25,621,215&nbsp;&nbsp; 23,968,673Jul 24&nbsp;&nbsp; 25,341,145&nbsp;&nbsp; 23,968,673Aug 24&nbsp;&nbsp; 23,048,609&nbsp;&nbsp; 23,968,673Sep 24&nbsp;&nbsp; 25,639,396&nbsp;&nbsp; 23,968,673Oct 24&nbsp;&nbsp; 24,680,700&nbsp;&nbsp; 23,968,673Nov 24&nbsp;&nbsp; 26,198,004&nbsp;&nbsp; 23,968,673Dec&nbsp;&nbsp; 26,071,045&nbsp;&nbsp; 23,968,673Average&nbsp;&nbsp; 23,968,673&nbsp;&nbsp;&nbsp;Payroll Expense Brand_Avg = &nbsp; VAR MonthlyTotals =&nbsp; &nbsp; SUMMARIZE(&nbsp; &nbsp; &nbsp; &nbsp; 'Main Data',&nbsp; &nbsp; &nbsp; &nbsp; 'Main Data'[Brand], &nbsp; &nbsp; &nbsp; &nbsp; 'Main Data'[Year], &nbsp; &nbsp; &nbsp; &nbsp; 'Main Data'[Month], -- 'Month' represents unique months&nbsp; &nbsp; &nbsp; &nbsp; "MonthlyTotal", SUM('Main Data'[Payroll])&nbsp; &nbsp; )&nbsp; VAR TotalSum = SUMX(MonthlyTotals, [MonthlyTotal])&nbsp; VAR MonthCount = DISTINCTCOUNT('Main Data'[Month]) -- Count unique monthsRETURNIF(&nbsp; &nbsp; MonthCount &gt; 0, &nbsp; &nbsp; TotalSum / MonthCount, &nbsp; &nbsp; BLANK())

anonymous · Answer

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

anonymous · Answer

Thanks for the reply from&nbsp;wini_R.
&nbsp;
Hi&nbsp;Anonymous&nbsp;,
&nbsp;
Based on my test, the formula provided by wini_R&nbsp;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.
&nbsp;

&nbsp;
&nbsp;
Best Regards,Zhu
&nbsp;
If there is any post&nbsp;helps, then please consider&nbsp;Accept it as the solution&nbsp;&nbsp;to help the other members find it more quickly.

Forum Discussion

KPI Visual target as average

6 Replies

How to Get Your Question Answered Quickly

Related content

Range and target visual

Comparing Actual vs Target Visually

Average and Moving Average

Gauge visual with multiple targets

Target visual

	Value	Target
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