Solved: IF formula with multiple conditions - Page 2

augustindelaf · ‎04-28-2017

Hi,

I would like to create a DAX formula with a IF statement.

my formula would be :

IF('DATA'[Work Stream ] ="WS 1.1";SUM('DATA'[KPI 2 Monthly Actual]);

IF('DATA'[Work Stream ] ="WS 2.1";SUM('DATA'[KPI 2 Monthly Actual]);

IF('DATA'[Work Stream ] ="WS 2.2";AVERAGE('DATA'[KPI 2 Monthly Actual]);

IF('DATA'[Work Stream ] ="WS 3.1";SUM('DATA'[KPI 2 Monthly Actual]);

IF('DATA'[Work Stream ] ="WS 3.4";SUM('DATA'[KPI 2 Monthly Actual]);

IF('DATA'[Work Stream ] ="WS 3.5";AVERAGE('DATA'[KPI 2 Monthly Actual])

Maybe it is possible with a look up Table ? (1st field : Work Stream, 2nd field : aggregation type)

anyway, even without look up Table it can be great !

Thank you for your Quick answer!

augustindelaf · ‎05-09-2017

a lady from the MS support gave me a solution that seems ok :

---

"

Hi Augustin,

Here are the measures that you will need:

SelectedValue = IF (ISFILTERED ( Example[Indicator1] ) && HASONEVALUE ( Example[Indicator1] );LASTNONBLANK ( Example[Indicator1]; 0 );"a default value");
SumValues = SUM(Example[Values]);
VarKPI2MonthlyTGTR = IF([SelectedValue]="WS"; AVERAGE(Example[Values]); [SumValues]);

In will need to substitute what is in orange with your dimensions.

And here are some interesting documentation:

"

---