-2

In my current project I am migrating Excel reports to Power BI, I need help converting the Excel formulas to DAX

Excel

COUNTIFS($K:$K,"*",$L:$L,E$31,$CS:$CS,1$D:$D,$E$15)

SUMIFS($AU:$AU,$L:$L,E$31,$CI:$CI, "Yes",$D:$D,$E$15)/1000

IFERROR((SUMIFS($AU:$AU,$L:$L,E$31,$CI:$CI, "Yes",$D:$D,$E$15,$P:$P," Key Launch Under Existing Hero")/1000)/E36,0)

COUNTIFS(`$BH$11:$BH$1001,"Commercial Innovation",$H$11:$H$1001,F$31,$C$11:$C$1001,$E$15,$BP$11:$BP$1001,"1")

SUMIFS($BB$11:$BB$1001$C$11:$C$1001,$E$15,$H$11:$H$1001,G$31,$BH$11:$BH$1001,"Commercial Innovation")/1000

COUNTIFS(OFFSET($L$10,0,0,10000,1),$D65,OFFSET($AL$10,0,INDEX($AL$6:$AU$6,MATCH($D$59,$AL$10:$AU$10,0)),10000,1),">0",OFFSET($CI$10,0,0,10000,1),"Yes",OFFSET($D$10,0,0,10000,1),$E$15)

IFERROR(AVERAGEIFS($CJ:$CJ, $K:$K, "*",$L:$L,$D113,$D:$D,$E$15), "-")
Nick is tired
  • 6,860
  • 20
  • 39
  • 51
elakiya selva
  • 11
  • 1
  • 2
  • Its very hard to tell from your example, a table would be better. you need to look at COUNT and SUM in DAX, using CALCULATE and FILTER So Excel COUNTIF(D:D, "Red") would be in power bi CALCULATE(COUNT(column), FILTER(table, column = "Red)) – Jon Sep 03 '19 at 14:24

1 Answers1

1

you can use Calculate function for any conditional calculation in DAX. There is no references and cells like excel in Power BI therefore there is no OFFSET or INDEX in Power BI but you can explain you problem in a table for find a solution instead of Offset and Index. For another formulas that you want:

Excel Countifs:

COUNTIFS($K:$K,"*",$L:$L,E$31,$CS:$CS,1$D:$D,$E$15)

DAX:

Measure Name = Calculate(count(K_Column), tbl_name[k_Column]="*",
tbl_name[L_Column]=E31_Value,...)

Excel Sumifs:

SUMIFS($AU:$AU,$L:$L,E$31,$CI:$CI, "Yes",$D:$D,$E$15)/1000

DAX:

Measure Name = Calculate(Sum(AU), tbl_name[L_Column]=E31_Value,
tbl_name[CI_Column]="Yes",...)

and you have iferror in DAX like excel:

iferror(calculate(sum(column_name), filter1, filter2))

There is a filter function in DAX for create a filtered table We don't have something like that in Excel.

Pouriya
  • 41
  • 3