Forum Discussion

PaulHallam's avatar
PaulHallam
Icon for Helper III rankHelper III
6 years ago
Solved

Help with SUMMARISE measure

Hi,

I have the following table 'DATA'

CATEGORYITEMDATETIMEVALUES
1A01/01/2020 09:00:001
1A01/01/2020 09:00:000
1A01/01/2020 09:00:001
1A01/01/2020 09:00:000
1B01/01/2020 09:00:000
1B01/01/2020 09:00:000
1B01/01/2020 09:00:000
1B01/01/2020 09:00:000
1A01/01/2020 09:05:001
1A01/01/2020 09:05:001
1A01/01/2020 09:05:001
1A01/01/2020 09:05:000
1B01/01/2020 09:05:000
1B01/01/2020 09:05:000
1B01/01/2020 09:05:000
1B01/01/2020 09:05:001
1A01/01/2020 09:10:000
1A01/01/2020 09:10:000
1A01/01/2020 09:10:000
1A01/01/2020 09:10:000
1B01/01/2020 09:10:000
1B01/01/2020 09:10:000
1B01/01/2020 09:10:000
1B01/01/2020 09:10:000

 

If the sum of the [VALUES] for an [ITEM] in any given 5 minute period >0 then the [ITEM] is deemed to have been used, so effectively creating this table below;

CATEGORYITEMDATETIMEVALUE
1A01/01/2020 09:00:001
1B01/01/2020 09:00:000
1A01/01/2020 09:05:001
1B01/01/2020 09:05:001
1A01/01/2020 09:10:000
1B01/01/2020 09:10:000


I want to know the utilisation of each item over the periods so;

ITEM A has been used on two of its three periods giving utilisation of 66.6%

ITEM B has been used on one of its three periods giving utilisation of 33.3%


Using this Measure in a matrix;

Utilisation% =
     (SUMX(
         SUMMARIZE(
            DATA,
            DATA[DATETIME],
                 "Count", if (SUM(DATA[VALUES])>0,1,if (SUM(DATA[VALUES])=0,0,0))),[Count]))/(DISTINCTCOUNT(DATA[DATETIME])*DISTINCTCOUNT(DATA[ITEM])

gives the correct utilisations at the individual [ITEM] level

BUT

at the [CATEGORY] level the measure is wrong showing 33.3% (2 uses out of 6) instead of (3 uses out of 6) 50.0%. This is because it is applying the measure at the [CATEGORY LEVEL] and when A & B are used together giving 2 uses at 09:005:00 it is converting this total to a 1.

Can anyone help to make the totals correct.

3 Replies