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
wini_R's avatar
wini_R
Solution Supplier
1 year ago

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'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