Sum values ​​by range

[rodfernandez]

Hi,

I need to see how many codes in the table on the right are within the range 0-100; 100-500; 500+ per "Month".
To do this I create the following measures

0-100 = IF(SUM(Distancia[Distancia / Kms])<100;1;BLANK())



100-500 = IF(SUM(Distancia[Distancia / Kms])<500 && SUM(Distancia[Distancia / Kms])>100;1;BLANK())



500+ = IF(SUM(Distancia[Distancia / Kms])>500;1;BLANK())

With this I can obtain the table on the left, but I cannot see the sum of the values, for example for "0", the category "0-100" for the month "5" I should see an 8 but the table is not adding the values.
How could you see the sum of the values ​​in the table?

[v-yingjl · Accepted solution]

Hi @rodfernandez ,

Based on your description, you can modfiy your 0-100 measure like this:

0-100 = 
VAR tab =
    SUMMARIZE (
        ALL ( 'Table' ),
        'Table'[Name],
        'Table'[Groupo2],
        'Table'[Mes],
        "Flag",
            IF (
                ISBLANK ( SUM ( 'Table'[Distancia / Kms] ) ),
                BLANK (),
                IF ( SUM ( 'Table'[Distancia / Kms] ) < 100, 1, BLANK () )
            )
    )
RETURN
    SUMX (
        FILTER (
            tab,
            [Flag] = 1
                && [Groupo2] IN DISTINCT ( 'Table'[Groupo2] )
                && [Mes] IN DISTINCT ( 'Table'[Mes] )
                && [Name] IN DISTINCT ( 'Table'[Name] )
        ),
        [Flag]
    )

[100-500] measure and [500+] measure are simliar with it.

Result:

Attached a sample file in the below, hopes to help you.

Best Regards,
Yingjie Li

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[Greg_Deckler]

@rodfernandez - I'm not sure I follow 100% but seems like you need to use SUMMARIZE and SUMX or something similar. This looks like a measure aggregation problem. See my blog article about that here: https://community.powerbi.com/t5/Community-Blog/Design-Pattern-Groups-and-Super-Groups/ba-p/138149

The pattern is:
MinScoreMeasure = MINX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
MaxScoreMeasure = MAXX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
AvgScoreMeasure = AVERAGEX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
etc.