cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
gvg
Post Prodigy
Post Prodigy

Create a SUM measure that sums conditionally (based on a value in another column)

Hi experts,

 

I have been using another table (tableB) to sum specific values in tableA using the following simple DAX:

Sum amount = CALCULATE ( SUM ( tableA[Amount] ), tableB[NewProducts] )

However this works only when Product_code in both tables is numeric. When Product_code is text, I get this error message: Cannot convert value "300a" of type Text to type True/False". How do I go around this?

 

My sample tables are like this:

 

tableA                                                    tableB
Product_code    Amount                                    Product_code
123a             100                                      125a
125a             120                                      300a
200              130
300a              90

 

1 ACCEPTED SOLUTION
gvg
Post Prodigy
Post Prodigy

OK, looks like this measure solves the issue:

 

Sum amount = CALCULATE ( SUM ( tableA[Amount] ), DISTINCT ( tableB[NewProducts]) )

View solution in original post

3 REPLIES 3
gvg
Post Prodigy
Post Prodigy

OK, looks like this measure solves the issue:

 

Sum amount = CALCULATE ( SUM ( tableA[Amount] ), DISTINCT ( tableB[NewProducts]) )

View solution in original post

Zubair_Muhammad
Community Champion
Community Champion

@gvg

 

Try

 

Sum Amount =
CALCULATE (
    SUM ( tableA[Amount] ),
    FILTER ( TableA, TableA[Product_code] = TableB[NewProducts] )
)

Regards
Zubair

Please try my custom visuals

@Zubair_Muhammad

Well, this syntax in Filter does not seem to be valid?

Helpful resources

Announcements
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors