SUMX

[DenbyG]

Good Morning,

I'm struggling with a simple calculation in Power Bi. I have a large table of employers. There is a column for each month of the year and the sum of funding for each month, and then there is a total column, which totals all funding for each employer. I would like to get the total sum for 3 specific employers, who I shall call, Employer A, Employer B and Employer C. Could someone help with the measure I need to achieve this? Thanks

[amitchandak · Accepted solution]

@DenbyG , Create a measure like 

Calculate(sum(Fact[Value]), filter(Fact, Fact[Employer] in {"Employer 1","Employer 2","Employer 3"}))

or with joined dim 

Calculate(sum(Fact[Value]), filter(Dim, Dim[Employer] in {"Employer 1","Employer 2","Employer 3"}))

Need the same value in when use employeer in group by 

Calculate(sum(Fact[Value]),  Fact[Employer] in {"Employer 1","Employer 2","Employer 3"})

in needed refer: Difference in filtering data in CALCULATE with and without FILTER
https://youtu.be/KDcmzwgPvXQ

[wardy912 · Accepted solution]

Hi @DenbyG 

Total Funding for A, B, C =
CALCULATE(
    SUM(Employers[Total]),
    Employers[Employer] IN {"Employer A", "Employer B", "Employer C"}
)

SUM(Employers[Total]) adds up the values in the Total column.
CALCULATE changes the filter context so it only includes rows where Employer is one of the three specified.
The IN operator makes it easy to check multiple values.

--------------------------------

I hope this helps, please give kudos and mark as solved if it does!

Connect with me on LinkedIn.

Subscribe to my YouTube channel for Fabric/Power Platform related content!

[Praful_Potphode · Accepted solution]

Hi @DenbyG ,

try measure below:

Total Funding Selected Employers = 
CALCULATE(
    SUM('Employers'[Funding]),
    FILTER(
        'Employers',
        'Employers'[Employer Name] = "Employer A" ||
        'Employers'[Employer Name] = "Employer B" ||
        'Employers'[Employer Name] = "Employer C"
    )
)

Please give kudos or mark it as solution once confirmed.

Thanks and Regards,

Praful

[Kedar_Pande · Accepted solution]

@DenbyG 

TotalFunding_ABC =
CALCULATE(
SUM('Employers'[Total]),
'Employers'[Employer] IN {"Employer A", "Employer B", "Employer C"}
)

If this answer helped, please click Kudos or Accept as Solution.
-Kedar
LinkedIn: https://www.linkedin.com/in/kedar-pande

[Praful_Potphode · Accepted solution]

Hi @DenbyG ,

try measure below:

Total Funding Selected Employers = 
CALCULATE(
    SUM('Employers'[Funding]),
    FILTER(
        'Employers',
        'Employers'[Employer Name] = "Employer A" ||
        'Employers'[Employer Name] = "Employer B" ||
        'Employers'[Employer Name] = "Employer C"
    )
)

Please give kudos or mark it as solution once confirmed.

Thanks and Regards,

Praful

[wardy912 · Accepted solution]

Hi @DenbyG 

Total Funding for A, B, C =
CALCULATE(
    SUM(Employers[Total]),
    Employers[Employer] IN {"Employer A", "Employer B", "Employer C"}
)

SUM(Employers[Total]) adds up the values in the Total column.
CALCULATE changes the filter context so it only includes rows where Employer is one of the three specified.
The IN operator makes it easy to check multiple values.

--------------------------------

I hope this helps, please give kudos and mark as solved if it does!

Connect with me on LinkedIn.

Subscribe to my YouTube channel for Fabric/Power Platform related content!

[amitchandak · Accepted solution]

@DenbyG , Create a measure like 

Calculate(sum(Fact[Value]), filter(Fact, Fact[Employer] in {"Employer 1","Employer 2","Employer 3"}))

or with joined dim 

Calculate(sum(Fact[Value]), filter(Dim, Dim[Employer] in {"Employer 1","Employer 2","Employer 3"}))

Need the same value in when use employeer in group by 

Calculate(sum(Fact[Value]),  Fact[Employer] in {"Employer 1","Employer 2","Employer 3"})

in needed refer: Difference in filtering data in CALCULATE with and without FILTER
https://youtu.be/KDcmzwgPvXQ