Forum Discussion
Problems with SUMIF equivalent
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
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.
4 Replies
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.
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.
Anonymous , I check fine. Not able to make much sense. Can you let me know Output in table format.
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 1Product 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
