Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Conditional SUMX formula

Hello, I am trying to create a SUMX function that will sum my rows by Category (Level 1 column) but only include rows in which a sector average is included.

 

Currently my formula of Population Summed = SUMX(VALUES('Table'[Level1]), CALCULATE([Population (excl. sector)])) is not working. I have included a picture below for reference but any assistance would be greatly appreciated. 

 

 

Need Help
  • v-henryk-mstf's avatar
    v-henryk-mstf
    4 years ago

    Hi Anonymous ,

     

    According to your description, try the following formula:

    Result = 
IF (
    'Table'[Sector Average] <> BLANK (),
    SUMX (
        FILTER (
            'Table',
            'Table'[Level 1] = EARLIER ( 'Table'[Level 1] )
                && 'Table'[Sector Average] <> BLANK ()
        ),
        [Population]
    ),
    BLANK ()
)


    If the problem is still not resolved, please point it out and let me know immediately. Looking forward to your reply.


    Best Regards,
    Henry


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

2 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    Anonymous , try like

     

    SUMX(addcolumns(Summarize('Table','Table'[Level1], 'Table'[Level2]), "_1", CALCULATE([Population (excl. sector)])),[_1])

  • v-henryk-mstf's avatar
    v-henryk-mstf
    Community Support
    4 years ago

    Hi Anonymous ,

     

    According to your description, try the following formula:

    Result = 
IF (
    'Table'[Sector Average] <> BLANK (),
    SUMX (
        FILTER (
            'Table',
            'Table'[Level 1] = EARLIER ( 'Table'[Level 1] )
                && 'Table'[Sector Average] <> BLANK ()
        ),
        [Population]
    ),
    BLANK ()
)


    If the problem is still not resolved, please point it out and let me know immediately. Looking forward to your reply.


    Best Regards,
    Henry


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