Forum Discussion
Display Number, only Number, and nothing but Number
I have the following data:
Location | Score | Goal |
Abc | 7.1 | 8.5 |
Xyz | 7.9 | 8.5 |
Lmn | 8.3 | 8.5 |
Pqr | 7.5 | 8.5 |
fgh | 8.4 | 8.5 |
I would really like to display this as a KPI that is NOT aggregated. It just displays the number, not an aggregate of any kind. When I put a filter on location Abc, I should see 7.1 in Red showing with an exclamation mark, a goal of 8.5 and (-1.4).
Barring that, if I could simply slam this number on to a Card that does nothing but display 7.1.
I can't figure out any way of making this happen. Both the card and KPI visuals seem to ignore any filter settings - visual, page, or report. Neither of these appears to have any concept of "all the calculations are done, just display the data I gave you without aggregating".
I have 23 locations. No matter how I apply filters, I get both the KPI and the Card displaying a COUNT = 23 even though every other visual - chart, matrix, table, map, etc. only displays data for a single location.
The only way I've found to actually make what I want work is to use a table that has a single row and a single column on it.
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...
9 Replies
No one has tried to display a KPI where the Indicator is not a COUNT, SUM, AVERAGE, MIN, MAX, etc., but is instead the last value? No one has tried to get a KPI to filter based on the report, page, or visual instead of ignoring all filtering and computing based on the entire data set?
If 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.
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...
I 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.
Since both myself and bblais got this to work, I suspect your Score field is not set as a numeric data type.
