Forum Discussion
Measure to show differential not displaying same information in table and card visual
Hello, my guess is that I am overlooking something simple and once I see the Solution, I will think 'duh'...
I have a table with trailer information. The data is showing trailers at specific locations (Landmarks). Each Landmark has a designated number of trailers (Pool Size) that 'should' be there, but the number of trailers does not always match the Pool Size.
For example, a Landmark may have a Pool Size of 25, but there may be 27 trailers there on a given day. In that case, the Pool Size Differential is 2; 27 - 25 = 2. My measure for the Pool Size Differential is: Pool Size Differential :=
DISTINCTCOUNT('Trailer Location (sql_dynamic)'[Asset ID]) - AVERAGE('Trailer Location (sql_dynamic)'[Pool Size])
Each trailer has an Asset ID and I have created a measurement to show the count of trailers: Number of Assets := COUNTROWS('Trailer Location (sql_dynamic)').
So, in my table visual, I am displaying the information by date and am comparing the Number of Assets to the Pool Size and I am seeing the correct results in the Pool Size Differential column.
| Landmark Name | Date | Number of Assets | Pool Size | Pool Size Differential | Pool Size % |
| DL - LATHROP - 25 - ADLLA | 5/7/2018 0:00 | 27 | 25 | 2 | 108% |
| DL - LATHROP - 25 - ADLLA | 5/14/2018 0:00 | 29 | 25 | 4 | 116% |
However, when I use the Multi-Card visual to display Landmark Type and Pool Size Differential, I do not get the same results. The visual is showing 21 compared to 2 (from the above table. My guess is it is because of the formula I am using for the Pool Size Differential measure. It works on an individual row of data, but not on the aggregate.
So, as a summary;
- I am counting assets at a Landmark with a measure; Assets per Landmark = COUNTROWS('Trailer Location (sql_dynamic)')
- I am calculating the Pool Size Differential as a measure;
- Pool Size Differential :=
DISTINCTCOUNT('Trailer Location (sql_dynamic)'[Asset ID]) - AVERAGE('Trailer Location (sql_dynamic)'[Pool Size])
- Pool Size Differential :=
- I have Pool Size data on each row of my table for that particualar Landmark. Hence, the reason I am using Average in my Pool Size Differential measure formula
Hope this is clear and if more information is needed, please ask.
Thanks in advance,
Jeff
2 Replies
- AnonymousNot applicable
Hi jhenscheid,
In my opinion, I'd like to suggest find out summarized unique row count, then you can use this to multiply with average poor size amount.
For example: date is the unique column of group.
Pool Size Differential := DISTINCTCOUNT ( 'Trailer Location (sql_dynamic)'[Asset ID] ) - AVERAGE ( 'Trailer Location (sql_dynamic)'[Pool Size] ) * CALCULATE ( DISTINCTCOUNT ( 'Trailer Location (sql_dynamic)'[Date] ), VALUES ( 'Trailer Location (sql_dynamic)'[Landmark Name] ) )BTW, DISTINCTCOUNT may get different result on summied level, it not equal to the sum of summarized row calculation result.
Regards,
Xiaoxin Sheng
- jhenscheidFrequent Visitor
Thank you for the suggestion. I tried it and even though it corrected the results in one visual, it broke the results in another visual...:) I think it comes down to trying to average one set of measurements by one date and presenting them a different way using another date range.
I went down the path of creating a summary table and linking it to this table, but again, didn't get what the end users were looking for. I will keep trying.
Thanks,
Jeff