Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
jhenscheid
Frequent Visitor

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 NameDateNumber of AssetsPool SizePool Size DifferentialPool Size %
DL - LATHROP - 25 - ADLLA5/7/2018 0:0027252108%
DL - LATHROP - 25 - ADLLA5/14/2018 0:0029254116%

 

 

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

 

 

 

2 REPLIES 2
v-shex-msft
Community Support
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.