Forum Discussion

ovetteabejuela's avatar
ovetteabejuela
Impactful Individual
9 years ago
Solved

KPI Visualization with Trend

Hi,   I'm trying to understand how the KPI visualization works because it changes the value once I insert a trend(ie Date).   Can somebody please help me understand this? Below on the left is wit...
Need Help
  • v-qiuyu-msft's avatar
    v-qiuyu-msft
    9 years ago

    Hi ovetteabejuela,

     

    It seems that you are using the custom visual KPI Indicator, right?

     

    Based on my test, if you don't specify the date column as trend, it will use total values of Completed and Target to calculate status ( 2995-4500)/4500=-33.4%.

     

     

    If date column is used as trend, it will calculate based on the latest date row follow this formula (211-300)/300=-29.7%.

     

     

    You can create DAX to calculate running total both for the Target and Completed:

     

    TargetRunningTotal = CALCULATE(SUM('Table1'[Target]),FILTER(ALL('Table1'),MAX('Table1'[Date])>='Table1'[Date]))
    CompeletedRunningTotal = CALCULATE(SUM('Table1'[Compeleted]),FILTER(ALL('Table1'),MAX('Table1'[Date])>='Table1'[Date]))

     

    After you add those two measure to the KPI indicator visual, you will get the same result whether add the date column as trend or not. As both the latest date row of running total Target and Completed value, and the total row of running total Target and Completed value are the same.

     

     

    If you have any other doubts about this visual, I would suggest you contact the author of this custom visual by sending email to [email protected].

     

    Best Regards,
    Qiuyun Yu

     

     

v-qiuyu-msft's avatar
v-qiuyu-msft
Community Support
9 years ago

Hi ovetteabejuela,

 

It seems that you are using the custom visual KPI Indicator, right?

 

Based on my test, if you don't specify the date column as trend, it will use total values of Completed and Target to calculate status ( 2995-4500)/4500=-33.4%.

 

 

If date column is used as trend, it will calculate based on the latest date row follow this formula (211-300)/300=-29.7%.

 

 

You can create DAX to calculate running total both for the Target and Completed:

 

TargetRunningTotal = CALCULATE(SUM('Table1'[Target]),FILTER(ALL('Table1'),MAX('Table1'[Date])>='Table1'[Date]))
CompeletedRunningTotal = CALCULATE(SUM('Table1'[Compeleted]),FILTER(ALL('Table1'),MAX('Table1'[Date])>='Table1'[Date]))

 

After you add those two measure to the KPI indicator visual, you will get the same result whether add the date column as trend or not. As both the latest date row of running total Target and Completed value, and the total row of running total Target and Completed value are the same.

 

 

If you have any other doubts about this visual, I would suggest you contact the author of this custom visual by sending email to [email protected].

 

Best Regards,
Qiuyun Yu

 

 

ovetteabejuela's avatar
ovetteabejuela
Impactful Individual
9 years ago

Excellent v-qiuyu-msft, But I would really prefer to have both the running average or total PLUS the historical data. 

 

Thank you for sharing the email, I think I would try to get in touch with the visual owner and see if he is able to give that option.