Measure for aggregation & range values

[TSGD2123]

Hello,

From a source table like:

Date	Id	Hours
01/01/2020	A	10
02/01/2020	A	8
03/01/2020	A	9
04/01/2020	B	7
05/01/2020	B	10
02/02/2020	A	11
03/02/2020	A	4
04/02/2020	B	3
05/02/2020	C	6
15/03/2020	A	8
16/03/2020	B	3
17/03/2020	C	2
18/03/2020	C	15

I'm trying to get a table with the count of Ids per Month and Id, clasified by range of values, so I'd end up with this:

Month	Ids whose monthly Sum Hours < 10	Ids whose monthly Sum Hours >= 10, <20	Ids whose monthly Sum Hours >20
Jan		1	1
Feb	2	1
Mar	2	1

I'm trying to figure out the measure(s) to get this, could you guys help, please?

Thanks

[Anonymous · Accepted solution]

Hi,

I copied your Data. My table name is Feuil 7

Here is the 3 measures :

less than 10 =

var __step1=filter(ADDCOLUMNS(SUMMARIZE(Feuil7,Feuil7[Id]),"@hours",calculate(sum(Feuil7[Hours]))),[@hours]<10)

return

COUNTROWS(__step1)

between 10 and 20 =

var __step1=filter(ADDCOLUMNS(SUMMARIZE(Feuil7,Feuil7[Id]),"@hours",calculate(sum(Feuil7[Hours]))),[@hours]>=10&&[@hours]<20)

return

COUNTROWS(__step1)

More than 20 =

var __step1=filter(ADDCOLUMNS(SUMMARIZE(Feuil7,Feuil7[Id]),"@hours",calculate(sum(Feuil7[Hours]))),[@hours]>20)

return

COUNTROWS(__step1)

[daXtreme · Accepted solution]

// You should have a Calendar table in your model
// with all the chunks of time that you desire.
// This is BEST PRACTICE and please stick to it
// if you don't want to have problems along the way.
// Once you've got this, you can write a measure...

// First, create a disconnected table in the model that'll
// store the categories, like so:

[Categories of IDs] = // calc table
SELECTCOLUMNS(
    {
        ("Sum Of Hours in [0, 10)", 0, 10),
        ("Sum Of Hours in [10, 20)", 10, 20),
        ("Sum of Hours in [20, ∞)", 20, 10000000000)
    },
    // Exposed column for categeries of the ID's
    "Category", [Value1],
    // Technical hidden column
    "SumLowerBoundInclusive", [Value2],
    // Technical hidden column
    "SumUpperBoundExclusive", [Value3]
)

// Then the measure... which will work for
// any piece of time, not only months.

[# IDs] =
SUMX(
    CROSSJOIN(
        'Categories of IDs',
        ADDCOLUMNS(
            VALUES( YourTable[Id] ),
            "@SumOfHours",
                CALCULATE( SUM( YourTable[Hours] ) )
        )
    ),
    var Hours = [@SumOfHours]
    var LowerBound = 'Categories of IDs'[SumLowerBoundInclusive]
    var UpperBound = 'Categories of IDs'[SumUpperBoundExclusive]
    var RawOutput = int( LowerBound <= Hours && Hours < UpperBound )
    var Output = if( RawOutput = 1, 1 )
    return
        Output
)

[Anonymous · Accepted solution]

Hi,

I copied your Data. My table name is Feuil 7

Here is the 3 measures :

less than 10 =

var __step1=filter(ADDCOLUMNS(SUMMARIZE(Feuil7,Feuil7[Id]),"@hours",calculate(sum(Feuil7[Hours]))),[@hours]<10)

return

COUNTROWS(__step1)

between 10 and 20 =

var __step1=filter(ADDCOLUMNS(SUMMARIZE(Feuil7,Feuil7[Id]),"@hours",calculate(sum(Feuil7[Hours]))),[@hours]>=10&&[@hours]<20)

return

COUNTROWS(__step1)

More than 20 =

var __step1=filter(ADDCOLUMNS(SUMMARIZE(Feuil7,Feuil7[Id]),"@hours",calculate(sum(Feuil7[Hours]))),[@hours]>20)

return

COUNTROWS(__step1)

[daXtreme]

@Anonymous 

Just want to point something out.. The problem with your solution is that it's not configurable through a table and when one will want to add another category, one will have to create another measure and update all visuals. This is not how it should be done because it can become a headache in the future. To do it properly one has to create a table of categories and then ONE AND ONLY ONE MEASURE that'll depend on the categories (please see my solution for details). Another problem with the solution above is that it's not aggregatable.

[TSGD2123]

Understood, in my case, with @Anonymous is enough but i'll apply yours as well in case of further requirements. 

Thank you both.

[TSGD2123]

Thanks man, works perfectly