Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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;
Hope this is clear and if more information is needed, please ask.
Thanks in advance,
Jeff
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
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
User | Count |
---|---|
141 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
123 | |
101 | |
71 | |
61 |