Measure to show differential not displaying same information in table and card visual

[jhenscheid]

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])
• 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

[v-shex-msft]

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

[jhenscheid]

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