Excel Sumifs to Dax - Help Greatly Appreciated

[Anonymous]

Hello!

In excel I have a SUMIfs. 

Col A is employee, B is week number and C is hours. 

In column D my sumifs would be:

SUMIFS(C:C,A:A,A2,B:B,B2) or sumifs(hours, employee column, A2,week number column, B2)

Employee	Week Number	Hours
Dave	1	4
Mark	1	5
Jacob	1	6
Dave	2	6
Jacob	1	5
Jacob	2	4
Mark	2	3

I am having desperate trouble replicating this formula in BI.

Any tips or help would be greatly appreciated.

Thanks

WATER

[v-xicai · Accepted solution]

Hi @Anonymous ,

You can try to create measure like DAX below.

Measure1= CALCULATE(SUM(Table1[Hours]),FILTER(ALLSELECTED(Table1), Table1[Employee] =MAX(Table1[Employee])&&Table1[Week Number] =MAX(Table1[Week Number])))

Best Regards,

Amy

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

[Anonymous]

Many thanks for your help!

Water-Hydration

[TomMartens]

Hey,

I'm not if I'm missing something, but I think it's not necessary to recreate the Excel formula. Here is a simple Matrix visual:

There is just one thing I changed after copying your sample data to Power BI, as the data type of the week column is recognized as numeric I changed the default summerization of this column to "Don't summarize", just mark the column and change the default summarization:

This is how Power BI works, it automatically groups categorical (non - numeric) columns and aggregates numerical values, for this reason it's necessary to change the default summarization of the week column.

Hopefully this is what you are looking for.

Regard,

Tom