Problems with SUMIF equivalent

[Anonymous]

Hello, 

I am trying to write an equivalent for SUMIF in PowerBI. I am using "less or equal" expression in two cases: with integer and with measure (which contains the same integer). And as a result, I suddenly get different answers (see measures Cumul & Cumul1 in attached file). 

Could you please help me solve this issue? What can be done to include measure to "less or equal" expression?

Many thanks in advance. 

The file can be found here: https://drive.google.com/file/d/1mW4SHrr6Yp-dDvXy-Gu45xVlGtLxiWeC/view?usp=sharing

[v-xicai · Accepted solution]

Hi @Anonymous ,

You may create measure like DAX below.

Cumul =CALCULATE(SUM(Sheet1[Value]),FILTER(ALLSELECTED(sheet1 ),Sheet1[Product]=MAX(Sheet1[Product]) &&Sheet1[Date] <=MAX(Sheet1[Date] ) ))

For the variable in measure or column  , you may refer to the link: https://radacad.com/caution-when-using-variables-in-dax-and-power-bi.

Best Regards,

Amy 

Community Support Team _ Amy

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

[amitchandak]

@Anonymous , I check fine. Not able to make much sense. Can you let me know Output in table format.

[Anonymous]

Dear @amitchandak, 

I’m sorry, I didn’t understand you clearly. Could you please give me more details of the information you need from me?

If to give a brief summary of the task, here it is. 

I have a table with sales of 2 products by weeks. I have a slicer for product and a slicer for week. 
Each week has its number (Code). 

I want to find a sum of sales of specific product (chosen by slicer) for all weeks from the first till the week which is selected in another slicer. 

I understand that if, for instance, Week 3 is selected, I should sum by all weeks which have numbers 1, 2 and 3 (so here 3 is the number of the last week that should be included). 

I think that my SUMIF formula should be like this:

Cumul = CALCULATE(SUM(Sheet1[Value]),FILTER(sheet1,Sheet1[Code_vlookup]<=[NoPeriod]),FILTER(sheet1,Sheet1[Product]=selectedvalue(Sheet2[Product])))

But this formula shows incorrect result. 

For the formula above, [NoPeriod] is the measure that shows number of week by its full name (3 for Week 3, 2 for Week 2 etc.). In my example, [NoPeriod] value is 3. 

But if I change [NoPeriod] for just integer 3 instead:
Cumul1 = CALCULATE(SUM(Sheet1[Value]),FILTER(sheet1,Sheet1[Code_vlookup]<=3),FILTER(sheet1,Sheet1[Product]=selectedvalue(Sheet2[Product])))

I'll get a different result (correct one). 

I am puzzling about the reason of such thing. 

The tables used are the following:

Sheet 1

Product	Date	Value
Tea	W1	1
Tea	W2	12
Tea	W3	223
Tea	W4	3304
Tea	W5	44005
Coffee	W1	2
Coffee	W2	23
Coffee	W3	334
Coffee	W4	4405
Coffee	W5	55006

Sheet2:

Product

Tea

Coffee

Sheet 3: 

Date	Code
W1	1
W2	2
W3	3
W4	4
W5	5

[Greg_Deckler]

@Anonymous - Probably is because of the context in which the measure is executing is throwing off the number. Often you can get around this by using a variable (VAR) at the start of a formula and setting it equal to your measure. This way you can often avoid context issues with having the measure buried in the formula somewhere.