Forum Discussion
Problems with SUMIF equivalent
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 , 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