Measure for aggregation & range values

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)

 

 

 

 

// 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
)