CODEWITHSUNDEEP
powerbidax
AR.
AR.

Reputation: 40525

DAX to conditionally sum from different columns?

I'm a bit lost on how to conditionally choose which of column to use in a sum, based on a value in a related table.Given the two tables below (with a relationship between the two on Widget IDs :

Widgets
ID  Color Shape
A   Red   Square
B   Red   Diamond
C   Blue  Square
D   Blue  Square
E   Red   Diamond
F   Blue  Diamond

Date WidgetID XValue YValue
1       A       5     10
1       C      15     20
3       D      25     30
3       F      35     40
4       A      45     50
4       B      55     60
4       E      65     70
4       F      75     80
5       D      85     90  
5       C      95    100

*Note the pattern of how X/YValue increment is irrelevant, just using unique numbers to show how totals are made below)

So I need to calculate totals according to these rules:

to end up with:

Color     TotalValue
Red          180    (i.e. 5+45+60+70)
Blue         340    (i.e. 15+25+40+80+85+95)

What's the DAX expression to calculate TotalValue?

Upvotes: 1

Views: 1047

Answers (2)

sergiom
sergiom

Reputation: 4877

It's possible to add separately the two shapes and then add the result. This can be done by adding a filter over the shape to the current filter context, twice, one per shape.

SumOfValues =
CALCULATE ( SUM ( WIdgetsValues[XValue] ), Widgets[Shape] = "Square" )
    + CALCULATE ( SUM ( WIdgetsValues[YValue] ), Widgets[Shape] = "Diamond" )

resulting table visual

Upvotes: 1

teylyn
teylyn

Reputation: 35905

Are you after an all-in-one DAX measure formula?

I would start earlier and shape that data in Power Query: merge in the shape value from the table with the shapes, then add a calculated column that determines row by row if X or Y is the value for that row. Then remove columns no longer required and load the result into the data model.

Depending on your situation, you might be able to remove the merged Shape and the XValue and YValue columns, so you end up with less data in the data model, despite using a helper column.

The DAX measure can then be a simple sum.

Upvotes: 1

Related Questions