COUNTIF & SUMIF in DAX

[0929041]

Dear readers,

I am facing a problem in my Excel data model. I would like to include the formulas COUNTIF & SUMIF in my data model which has to be written in DAX.

The goal I want to achieve is to sum up the quantities per ref. nr. in the data model, and to count the amount of the same ref. numbers.

Note: This is a replicated version of the real dataset and is for ease made in Excel.

 

[mart37]

DAX SUM IF:=
VAR Ref = Table1[Ref. nr.]
RETURN CALCULATE(SUM(Table1[Quantity]);FILTER(Table1;Table1[Ref. nr.]=Ref))

DAX COUNT IF:=
VAR Ref= Table1[Ref. nr.]
RETURN CALCULATE(COUNTROWS(Table1);FILTER(Table1;Table1[Ref. nr.]=Ref))

 

[mart37]

Measures:

SumIf:=CALCULATE(SUM(Table1[Quantity]);FILTER(Table1;Table1[Ref. nr.]=Table1[Ref. nr.]))

CountIf:=CALCULATE(COUNTROWS(Table1);FILTER(Table1;Table1[Ref. nr.]=Table1[Ref. nr.]))