Sum of a measure when the conditional depends on other measure

[Smackcat]

Hi experts

I have one table (Table #1)

Week	Offer_price	Actual_price	Count_days	Sales
Week 1	5	10	2	400
Week 1	10	10	5	500
Week 2	10	10	5	700

First, I calculated whether an offer exists or doesn't exist on a week:

Offer = if(AVERAGE(Table[Offer_price]) < MAX(Table[Actual_price]), 1 , 0)

The measure [Offer] is a binomial value, and it's calculated based on Table #1. I also have two visuals:

Visual #1:

	Offer	1 (Offer exists)	0 (Offer doesn't exist)
Week 1	Surplus	200	0
Week 2	Surplus	0	0

The value shown on Visual #1 is a measure [Surplus] that's the value of the surplus calculated whether an offer exist or doesn't exist on a week. It sums 100 if we have one day with an offer. In Visual #1 we have 2 days with an offer (of 7 days on the week), so we have a Surplus of 100 * 2 = 200.

Surplus = if([Offer] = 1, 100 * Table[Count_days], 0)

*Actually, this measure in real life is way more complex than this, so the [Surplus] value has to be a measure, not a column

What I want to do is get a sum for the entire surplus of the week, considering that there are days with and without an offer. So my desired result is this:

Visual #2 --> Desired result

	Week 1	Week 2
Surplus	200	0

Neverthless, as I'm using a measure [Offer] based on the average, when I see the results on that week, I get an offer for the entire week and not on separate days.

Visual #2 --> Actual result

	Week 1	Week 2
Surplus	700	0

I know my mistake is based on the average part based on the [Offer], but I don't know how to solve it to make it count row by row. 

Any advice on this? Thanks

[Greg_Deckler]

@Smackcat First, please vote for this idea: https://ideas.powerbi.com/ideas/idea/?ideaid=082203f1-594f-4ba7-ac87-bb91096c742e

This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

Also: https://youtu.be/uXRriTN0cfY
And: https://youtu.be/n4TYhF2ARe8

[rubayatyasmin]

@Greg_Deckler I really thought of mentioning you when I saw this post. Though I voted for it a long time ago. 

[rubayatyasmin]

Hi, @Smackcat 

The issue you're experiencing arises from the aggregation level. Your measures are being evaluated at the level of each visual, not at the row level.

To achieve your desired result, you could consider iterating row by row within your measure, using a DAX function such as SUMX or CALCULATE. These functions can iterate through the rows of the table, and evaluate the expression you provide for each row. For example

Surplus =
SUMX(
Table,
IF(
Table[Offer_price] < Table[Actual_price],
100 * Table[Count_days],
0
)
)