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

Compete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.

Reply
rodfernandez
Helper I
Helper I

Sum values ​​by range

Hi,

 

Captura.PNG

 


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?

1 ACCEPTED SOLUTION
v-yingjl
Community Support
Community Support

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:

count.png

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.

View solution in original post

2 REPLIES 2
v-yingjl
Community Support
Community Support

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:

count.png

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
Community Champion
Community Champion

@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.



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
DAX For Humans

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
August Power BI Update Carousel

Power BI Monthly Update - August 2025

Check out the August 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

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

Top Solution Authors