Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 with trend (Date) and on the right has no trend(actual value throughout the month)
Solved! Go to Solution.
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 fredrik.hedenstrom@outlook.com.
Best Regards,
Qiuyun Yu
Just created a suggestion for new custom visual in the new 'Custom visual blog' released recently :
feel free to support !
The Card with States visual allows you to sum by values which should be a feature in the other KPI visuals, save having to build workarounds
With the KPI visual, the main number shown will always be the latest value in order of date. The purpose is to show the current number. So once you add a date column to your Trend property, the KPI can scan your dates and display the value shown for the latest date.
Hi @Phil_Seamark,
You're right, it was the latest figure. I couldn't find a setting that can show the running % but could it be done?
I wanted to show the running % instead of the current one.
A running total (absolute or % values) is a common DAX pattern:
http://www.daxpatterns.com/cumulative-total/
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).
Can anyone here answer if the trend depicted in the KPI visual would work with a running total?
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.
@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 fredrik.hedenstrom@outlook.com.
Best Regards,
Qiuyun Yu
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.
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 |
Running % would probably be a different measure than the one you are using for point-in-time. Look into TOTALYTD function.
Hope this helps.
David
For the visual WITH the trend, try setting a visual level filter for Date to the same date range (12/1 - 12/31) and see if it gives you the same value as the other KPI.
The other possibility is that their is something up with the measure you are using to calculate your KPI.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
126 | |
106 | |
105 | |
86 | |
72 |