cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mhotek Frequent Visitor
Frequent Visitor

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.

1 ACCEPTED SOLUTION

Accepted Solutions
bblais Member
Member

Re: Display 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...

 

Capture.PNG

 

View solution in original post

9 REPLIES 9
mhotek Frequent Visitor
Frequent Visitor

Re: Display Number, only Number, and nothing but Number

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?

Microsoft dkay84_PowerBI
Microsoft

Re: Display Number, only Number, and nothing but Number

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.

bblais Member
Member

Re: Display 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...

 

Capture.PNG

 

View solution in original post

mhotek Frequent Visitor
Frequent Visitor

Re: Display Number, only Number, and nothing but Number

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.

Microsoft dkay84_PowerBI
Microsoft

Re: Display Number, only Number, and nothing but Number

Since both myself and @bblais got this to work, I suspect your Score field is not set as a numeric data type.

Microsoft dkay84_PowerBI
Microsoft

Re: Display Number, only Number, and nothing but Number

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 Member
Member

Re: Display Number, only Number, and nothing but Number

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:

 

Capture.PNG

mhotek Frequent Visitor
Frequent Visitor

Re: Display Number, only Number, and nothing but Number

I have it working, although I have no idea what is going on.  All it would do is throw an aggregate any time I tried to stick the score in the Indicator or the target goals.  I had to do the following:

1. Go to the model

2. Double click the relationship

3. Change it to 1:1 and a cross filter direction to Both

4. Hit enter when it gave me the "cross filter direction is invalid" error message to force it to create it

5. Edit the relationship

6. Change it back to 1:Many with a cross filter direction of single

 

Now, my indicator just shows as the column, not Count of Score, Sum of Score, etc.  The Target Goals just shows as the column instead of Count of Goal, Sum of Goal, etc.

 

I have no idea what editing, forcing a relationship creation, reediting to put it back the way it should be did to PowerBI, but it now works as I expect it to.

mhotek Frequent Visitor
Frequent Visitor

Re: Display Number, only Number, and nothing but Number

I actually obeys my Report, Page, and Visual filters now as well, no need for a slicer.  Before, I hacked at the relationship between the two tables, it ignored any report, page, and visual filters.

Helpful resources

Announcements
New Kudos Received Badges Coming

New Kudos Received Badges Coming

Kudos to you if you earned one of these! Check your inbox for a notification.

Microsoft Implementation for Communities Wins Award

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Kudoed Authors (Last 30 Days)
Users online (2,580)