Forum Discussion
Including data in visual for non-existing rows
Hello!
I'm trying to get a Gauge visual to work and can't figure something out.
All set fields (Value, Maximum value and Target value) are working properly except for a case where data is missing.
In this example, 217.50 is the sum of 6 rows instead of 7, the real number should be 255.
Data for Maximum value should is:
| User ID | Time |
| 1 | 30 |
| 2 | 37.5 |
| 3 | 37.5 |
| 4 | 37.5 |
| 5 | 37.5 |
| 6 | 37.5 |
| 7 | 37.5 |
The reason I believe why it is not showing is that there are no rows (not empty rows, 0 rows) associated to the Value field for one of the rows above.
To make things simple, let's say the Value data is the following:
| User ID | Spent |
| 1 | 23 |
| 2 | 23 |
| 3 | 23 |
| 4 | 23 |
| 5 | 23 |
| 6 | 23.75 |
Since there is no Value data for User ID 7, the associated Time from table 1 doesn't seem to be calculated in the Maximum value field.
So far, the only options seems to inject a row in the Spent table for User ID = 7, even if 0 or null, in order for the rest to follow. This is not possible in the context of the actual report as it will generate false data.
I've also tried using ALL/ALLEXCEPT/REMOVEFILTERS, to no avail.
Any ideas?
3 Replies
Hi golbarg ,
Can you provide a little more information about how your data is structrued?
I am getting the desired result with 2 tables joined on User ID. How is yours different?
Sure, here goes:
Targets UserID Target 1:1
Users UserID TeamID Time [Measure] Target = (Time - Spent(Category>Name = A)) * Target 1:* (both directions)
Time UserID ProjectID_A Spent *:1
Projects (A) ID 1:* (Both directions)
Projects (B) ID ProjectID_A CategoryID *:1
Categories ID Name
The Gauge Visual is on a Drill through page for a team (filtered by Users>TeamID). The Visual also has a filter applied for Categories>Name = B.
Value is the Sum of Time>Spent
Maximum value is the Sum of Users>TimeTarget value is a measure
Essentially, I would like the Gauge to display Spent time in Category B, with a target that excludes time spent in Category A, and set a Maximum value of the sum of all the team's users' time.
I have another Drill through page for Users with the same Gauge Visual, and for a user where there is no Spent in the selected category, the visual shows up blank:There is Spent time in other Categories for that user (let's call them C and D).
I was able to make it work by using a measure instead of the existing column for the Value. The measure is pretty much a sum that will return 0 if no results are met, thus forcing the rest to follow.
