KPI Visual target as average

[Anonymous]

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

[Anonymous]

Hi @Anonymous ,

Have you solved your problem? If it is solved, please share your solution and accept it as solution or mark the helpful replies, it will be helpful for other members of the community who have similar problems as yours to solve it faster. Thank you very much for your kind cooperation!

Best Regards,
Zhu

[wini_R]

Hey @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  into the issue

How to Get Your Question Answered Quickly

[Anonymous]

see below, thank you

	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

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]

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]

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

[Anonymous]

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.