Forum Discussion
Advocate IDisplay Number, only Number, and nothing but Number
Add a measure that is just SUM(Score), I called mine Curr Score. Then use Curr Score as the indicator, score as the trend axis and goal as the target goal value...
Microsoft EmployeeIf I put Score in a card and select a location via a slicer, it shows the result (7.1) no problem.
As for a KPI, the preloaded KPI visual doesn't seem to do the trick, but the custom visual here: https://app.powerbi.com/visuals/show/KPIStatusWithHistory1446562283967 doesn't need a trend value and shows exactly what you are describing (no exclamation point, but visual indicator - red orange yellow green - and the variance)
The only caveat is that if no slicer is selected, then it will aggregate the scores. I tried creating a measure:
Scores = IF(HASONEFILTER(Table1[Location]),VALUES(Table1[Score]),BLANK())
to return a blank if no slicer is selected, but the KPI just shows red with -100% so that won't work either.
Advocate II tried that. I created a slicer with the location and added a card with the value. Selecting one of the locations in the slicer filters every visual on the page, except the card. The card stays with an aggregate across all values in the table. I simplified my table structure the actual is like this:
LocationID | Location |
1 | Abc |
2 | Xyz |
3 | Lmn |
4 | Pqr |
5 | Fgh |
LocationID | ScoreDate | Score | Goal |
1 | 20170319 | 7.5 | 8.5 |
2 | 20170319 | 7.4 | 8.5 |
3 | 20170319 | 8.2 | 8.5 |
4 | 20170319 | 7.6 | 8.5 |
5 | 20170319 | 8.5 | 8.5 |
1 | 20170326 | 7.1 | 8.5 |
2 | 20170326 | 7.9 | 8.5 |
3 | 20170326 | 8.3 | 8.5 |
4 | 20170326 | 7.5 | 8.5 |
5 | 20170326 | 8.4 | 8.5 |
I put a slicer that displayed the location and added a card that displayed the Score column. With nothing selected, I get 10 (count of Score) which is the default. Changing it to any other aggregate has it simply calculate that aggregate over all 10 values. Selecting location Abc in the slicer, filters everytihng else on the page, but does not filter or change the value on the card. Removing the table and replacing it with a view that only returns the most recent value for each location:
LocationID | ScoreDate | Score | Goal |
1 | 20170326 | 7.1 | 8.5 |
2 | 20170326 | 7.9 | 8.5 |
3 | 20170326 | 8.3 | 8.5 |
4 | 20170326 | 7.5 | 8.5 |
5 | 20170326 | 8.4 | 8.5 |
Still does nothing. The slicer has no effect on what is displayed on the card. Just as adding a report, page, or visual filter has no effect.
- dkay84_PowerBI
Since both myself and bblais got this to work, I suspect your Score field is not set as a numeric data type.
- dkay84_PowerBI
Also, thank you bblais for showing me something I never knew! I always thought the trend field of the KPI visual needed to be a date field. Never thought to dump something else in there (especially since you can turn off the trend axis).
- bblais
Resolver III
If you have Locations and LocationScores tables, you can use the ScoreDate as the trend axis and it should use the "most recent" like this: