Forum Discussion
KPI Visualization with Trend
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
However, I think you may be best served by using the calculated running total in a bar/line chart and creating a separate card visual for the KPI value (rather than using the KPI visual itself).
I was actually hoping to minimize the use of visualizations on a page. It would have been great if the KPI visual has that option to show the running total instead of the rightmost value(it's not the most recent because when I sorted the date in decending order it took the value/% of the first day of the month rather than the latest day - so it's the rightmost entry).
Thank you by the way for pointing me out to DAX patterns, another reason why I should start digging in to that topic - I know it's there but I haven't started reading about it yet.
I'm thinking that if you have a running total value by date, then using that value in the KPI visual with the date for trend will give you what you want (the most recent day's value would be the current running total) and the trend would depict how your running total has performed over the time period.
I can't share the actual data so I made a smaller version but this yields the same behaviour:
Date Employee Completed Target 12/1/2016 12345 51 100 12/2/2016 12345 63 100 12/3/2016 12345 77 100 12/4/2016 12345 37 100 12/5/2016 12345 91 100 12/6/2016 12345 96 100 12/7/2016 12345 82 100 12/8/2016 12345 26 100 12/9/2016 12345 59 100 12/10/2016 12345 53 100 12/11/2016 12345 79 100 12/12/2016 12345 51 100 12/13/2016 12345 83 100 12/14/2016 12345 88 100 12/15/2016 12345 65 100 12/1/2016 55342 29 100 12/2/2016 55342 69 100 12/3/2016 55342 87 100 12/4/2016 55342 51 100 12/5/2016 55342 90 100 12/6/2016 55342 91 100 12/7/2016 55342 72 100 12/8/2016 55342 86 100 12/9/2016 55342 25 100 12/10/2016 55342 33 100 12/11/2016 55342 45 100 12/12/2016 55342 86 100 12/13/2016 55342 97 100 12/14/2016 55342 50 100 12/15/2016 55342 82 100 12/1/2016 33452 63 100 12/2/2016 33452 84 100 12/3/2016 33452 61 100 12/4/2016 33452 34 100 12/5/2016 33452 84 100 12/6/2016 33452 90 100 12/7/2016 33452 89 100 12/8/2016 33452 42 100 12/9/2016 33452 49 100 12/10/2016 33452 28 100 12/11/2016 33452 79 100 12/12/2016 33452 100 100 12/13/2016 33452 46 100 12/14/2016 33452 88 100 12/15/2016 33452 64 100 dkay84_PowerBI wrote:
I'm thinking that if you have a running total value by date, then using that value in the KPI visual with the date for trend will give you what you want (the most recent day's value would be the current running total) and the trend would depict how your running total has performed over the time period.
I like the idea, but wondering how the graph would look like.... the graph would probably depict the running total per day and not the daily totals.. Hmmmm... I will try that out(if I can).
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 YuExcellent 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.