Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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 resul...
Need Help
  • v-xicai's avatar
    v-xicai
    6 years ago

    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's avatar
amitchandak
Super User
6 years ago

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

Anonymous's avatar
Anonymous
Not applicable
6 years ago

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
ProductDateValue
TeaW11
TeaW212
TeaW3223
TeaW43304
TeaW544005
CoffeeW12
CoffeeW223
CoffeeW3334
CoffeeW44405
CoffeeW555006

 

Sheet2:

Product
Tea
Coffee

 

Sheet 3: 

DateCode
W11
W22
W33
W44
W55