How to Calculate Sum from a Revenue column and being filter by another column

[lorenz0210]

Hi everyone, 

I am trying to do a break out of the revenue using different columns and filter. I am trying to seperate the revenue between region hunting and farming. 

 Here is the dax I created: 

Regional Account Hunting =

CALCULATE(SUM('2024 - Sales Performance (S+V)'[Finance Projection 2024 (Split) (converted).amount]),

    Filter('2024 - Sales Performance (S+V)',

        '2024 - Sales Performance (S+V)'[Regional Account Flag]="EMEA" ||

        '2024 - Sales Performance (S+V)'[Regional Account Flag]="US Market"

        && '2024 - Sales Performance (S+V)'[User TP Selling Role] IN {"Hunting"}

    )

)

For some reason it is not working and not calculating correctly. 

Hoping someone can help.

Appreciate much. 

[lorenz0210]

Hello everyone again, 

I tried this dax just to make sure numbers are calculating correctly: 

Regional Account_US Market =

    CALCULATE(SUM('2024 - Sales Performance (S+V)'[Finance Projection 2024 (Split) (converted).amount]),

    Filter('2024 - Sales Performance (S+V)',

        '2024 - Sales Performance (S+V)'[User TP Selling Role]="Hunting" &&

        '2024 - Sales Performance (S+V)'[Regional Account Flag]="US Market"

    ))

However the challenge is the dax on how to add the revenue for "EMEA" that is still within the "User TP Selling Role"- "Hunting". 

[Kaviraj11]

You can use Matrix Visuals, keep region in Rows, keep selling roles in columns and finance projection in values. 

you can filter out the region and selling roles that you don't want in the filter pane

[Kaviraj11]

Can you share the pbix file and expected output?

[lorenz0210]

Here is the expected output: 

[Anonymous · Accepted solution]

Hi @lorenz0210 

You can try the following measure

Regional Account Hunting =
CALCULATE (
    SUM ( '2024 - Sales Performance (S+V)'[Finance Projection 2024 (Split) (converted).amount] ),
    FILTER (
        '2024 - Sales Performance (S+V)',
        '2024 - Sales Performance (S+V)'[Regional Account Flag]
            IN { "EMEA", "US Market" }
                && '2024 - Sales Performance (S+V)'[User TP Selling Role] IN { "Hunting" }
    )
)

Best Regards!

Yolo Zhu

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

[lorenz0210]

Hi Yolo,

Thank you. The dax works. Although one more dax I am trying to solve: 

Is there a simple way to fix this: 

Hunting S+V =

    CALCULATE(SUM('2024 - Sales Performance (S+V)'[Finance Projection 2024 (Split) (converted).amount]),

    Filter('2024 - Sales Performance (S+V)',

        '2024 - Sales Performance (S+V)'[User TP Selling Role]="Hunting" &&

        '2024 - Sales Performance (S+V)'[Regional Account Flag]<>"EMEA" &&

        '2024 - Sales Performance (S+V)'[Regional Account Flag]<>"US Market" &&

        '2024 - Sales Performance (S+V)'[Global Account Flag]<>"AMERICAS;" &&

        '2024 - Sales Performance (S+V)'[Global Account Flag]<>"EUROPE & APAC;"

    ))

[Anonymous]

Hi @lorenz0210 

You can try the following measure.

Hunting S+V =
CALCULATE (
    SUM ( '2024 - Sales Performance (S+V)'[Finance Projection 2024 (Split) (converted).amount] ),
    FILTER (
        '2024 - Sales Performance (S+V)',
        '2024 - Sales Performance (S+V)'[User TP Selling Role] = "Hunting"
            && NOT ( '2024 - Sales Performance (S+V)'[Regional Account Flag]
            IN { "EMEA", "US Market", "AMERICAS", "EUROPE & APAC" } )
    )
)

Best Regards!

Yolo Zhu

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