Need to replicate an Excel SUMIF formula in Power Bi DAX

[Mauri_Castro_70]

Hi Everyone!

I need your help in coming up with an equivalent SUMIF (from excel) formula in DAX either for Calculated Column or even better, for a Measure. So, in simple words I need a measure that will give me the same result as in the table below using excel SUMIF; where such formula is using Column [ID] as the RANGE, ROW H2 as the CRITERIA and finally column [SUM] as the SUM_RANGE. Is this even posible in DAX; I sure hope so. I Tried something like this: 

SUMIF = CALCULATE(SUM('MyDate'[Sum]), ALLEXCEPT('MyData','MyData'[ID]))

But the DAX above is not giving me the same result as in excel. And I know that the excel formula is the correct one.

Sorry for the probable basic question but I am still a newbie in PBI.

Please Help!

Thanks in adavance!

Mcastro

[Ashish_Mathur]

Hi,

This calculated column formula should work

SUMIF = calculate(sum(Data[sum]),filter(Data,Data[ID]=earlier(data[ID])))

Hope this helps.

[Mauri_Castro_70]

@Ashish_Mathur thanks fo much for the reply.

I tried your formula in a new Measure and it returned the following error: "EARLIER/EARLIEST refers to an earlier row context which doesn't exist." When I tried it in a new Calculated Column it returned this error: "Function 'CALCULATE' is not allowed as part of calculated column DAX expressions on DirectQuery models."

I mentioned in one of my replies that I am working on a Direct Query  and not Import Mode

Thanks again for the help Ashish!

[Ashish_Mathur]

Hi,

For us to write a measure, we will have to insert a field in the visual which will differentiate each row.  As of now (from the image that you have shared), all rows are exactly the same.

[Mauri_Castro_70]

@Ashish_Mathur Thanks again for replying to this thread.

You are correct... in the image I shared all the rows are the same; I just included one ID number (IM-213313). However, in the actual data the ID is unique but the same ID can found many times (hundreads of times) along the fiscal year period. This is the reason why we need to replicate what the SUMIF formula does in excel so that we can obtain the total SUM of all those rows for each ID (just like it shows in the image I shared). Now the initial formula I used should and does work to an extend, however; the result of the formula keeps showing the sum of all rows with that ID in the source data even though I have filtered the data in Power Query and for that specific ID I should be getting the sum of only 36 instances and not 3.540 found in the source data. Hope this makes sense; any help will be greatly appreciated.

Thanks!

[Ashish_Mathur]

Perhaps someone else will help you.

[ryan_mayu · Accepted solution]

@Mauri_Castro_70 

it should work. What's the result that you get?

[Mauri_Castro_70]

@ryan_mayu Thanks so much for the reply. 

I have been trying to figure out why isn't working for me in PBI. I finally got it!... even though I made several data transformations in power query in order to reduce the size of the data (among other things) I would be working with in PBI; the Measure above is actually reading the whole data source (set). In other words, if I am getting only 36 rows with the value = IM-213313, the formula is still calculating on the actual 3,504 rows that exist in the data set with that same number (IM-213313). So, I really don't know how to fix that... any ideas?

BTW, I am connected to an SQL database table (Direct Query); Not import Mode.

Thanks again for your help time