Sum of values in column 1 if column 2 is equal

[Roedie84]

Hi All, I am new here and have a question about PowerBI

I like to have a measure ( if possible ) to calculate the sum from column 1 if the values in column 2 are equal.

I am new with PowerBI so I hope someone can help me.

[v-xuding-msft]

Hi @Roedie84 ,

You could try the following formula.

Measure = IF(MAX(column 1) = MAX(column 2), SUM(column 1),0)

If this is not what you want, can you please post some sample data and the excepted output?

Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

Best Regards,

Xue Ding

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

[BekahLoSurdo]

Hi @Roedie84,

Will this work?

CurrencyByDate = CALCULATE( 
    SUMX( 'Table', 'Table'[Currency Price] ),
    ALLEXCEPT( 'Table', 'Table'[Status31_Max_Date] ) 
)

This groups your currency values by date and gives the total sum of each group.

Best,

Bekah

[Anonymous]

Hi @Roedie84 , could you show your whole table (at least all the columns, not necessary all the lines) and the expected result ? 

I didn't understand what you're expecting from your screenshot

Regards, 

Etienne

[Roedie84]

 What I expect is:

For each date thats available in the column Status21_Max_Date a Sum of the Currency Price.

so if 29-07-2019 occurs 2 times with currency price 25 and 75 than the sum for 29-07-2019 = 100.

The possible solutions mentioned above don't work.

[v-xuding-msft]

Hi @Roedie84 ，

I modified the formula you can have a try.

Measure = CALCULATE(SUM('Table'[Column2]),FILTER(ALL('Table'),'Table'[Column1] = MAX('Table'[Column1])))

Best Regards,

Xue Ding

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

[Roedie84]

---

@v-xuding-msft wrote:

Hi @Roedie84 ，

I modified the formula you can have a try.

Measure = CALCULATE(SUM('Table'[Column2]),FILTER(ALL('Table'),'Table'[Column1] = MAX('Table'[Column1])))

Best Regards,

Xue Ding

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

---

Thnx for your changed formula! Unfortunatly I get the following error Right now:

The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value.

[BekahLoSurdo]

Hi @Roedie84,

Have you tried the formula I posted? I think it is what you are looking for. Here it is modified with the sample information from this thread:

Measure = CALCULATE( 
    SUMX( 'Table', 'Table'[Column2] ),
    ALLEXCEPT( 'Table', 'Table'[Column1] ) 
)