cancel
Showing results for
Did you mean:  Resolver I

## How to convert COUNTIFS Excel formulae to DAX to get summarised data

Please advice if you have can on how to convert Below table raw data which I have result table with DAX

I have raw data of the countries which sports they have, and I have summarised data with on the table using COUNTIFS function.

I would like to recreate the same table in Power BI using DAX function and get the same results.

The table summarised which sport country won and also on column N3 to N5 count how many Draws for last 10 days based on date column ("<"&TODAY()-50))

Please see below formulae which need to be converted to  DAX

Summarised formulae

• =COUNTIFS(\$B\$4:\$B\$34,\$L\$3,\$D\$4:\$D\$34,\$N\$2)
• =COUNTIFS(\$B\$4:\$B\$34,\$L\$3,\$D\$4:\$D\$34,\$N\$2,\$E\$4:\$E\$34,("<"&TODAY()-50))
• =O3/(O3+P3)
• =O3/(O3+P3+N3)

Please see attached Excel file which need to be converted to DAX formulae to get the summarised data on small table.

Excel file:with formulas on summarised table

https://app.box.com/s/z15jvl1tlfhm18jlacghdi6nbu3wk3h8

Power BI file/pbix

https://app.box.com/s/dfzieiqtw7r9dofa53pbqb30zqycl6yj

1 ACCEPTED SOLUTION  Community Support

hi, @MYDATASTORY Second, why "which sport country won" is USA? if it is the max [DRAW] of all country?

If so, please use these formula to create the measure

```won country = var _maxdraw= CALCULATE(MAXX(VALUES(Table3[Country ]),[DRAW]),ALL(Table3)) return
var _maxcountry=CALCULATE(MAXX(FILTER(VALUES(Table3[Country ]),[DRAW]=_maxdraw),[Country ]),ALLSELECTED(Table3)) return
_maxcountry```
`DRAW = CALCULATE(COUNTA(Table3[Country ]),FILTER(Table3,Table3[Status]="DRAW"))+0`
`More than 50days DRAW = CALCULATE(COUNTA(Table3[Country ]),FILTER(ALLSELECTED(Table3),Table3[Country ]=[won country]&&Table3[Status]="DRAW"&&Table3[Date ]<TODAY()-50))+0`
`WIN = CALCULATE(COUNTA(Table3[Country ]),FILTER(ALLSELECTED(Table3),Table3[Country ]=[won country]&&Table3[Status]="WIN"))+0`
`LOST = CALCULATE(COUNTA(Table3[Country ]),FILTER(ALLSELECTED(Table3),Table3[Country ]=[won country]&&Table3[Status]="LOST"))+0`
`WIN% = DIVIDE([WIN],[WIN]+[LOST])`
`More than 50days win% = DIVIDE([WIN],[WIN]+[LOST]+[More than 50days DRAW])`

Result: and here is pbix file, please try it.

Best Regards,

Lin

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

hi, @MYDATASTORY Second, why "which sport country won" is USA? if it is the max [DRAW] of all country?

If so, please use these formula to create the measure

```won country = var _maxdraw= CALCULATE(MAXX(VALUES(Table3[Country ]),[DRAW]),ALL(Table3)) return
var _maxcountry=CALCULATE(MAXX(FILTER(VALUES(Table3[Country ]),[DRAW]=_maxdraw),[Country ]),ALLSELECTED(Table3)) return
_maxcountry```
`DRAW = CALCULATE(COUNTA(Table3[Country ]),FILTER(Table3,Table3[Status]="DRAW"))+0`
`More than 50days DRAW = CALCULATE(COUNTA(Table3[Country ]),FILTER(ALLSELECTED(Table3),Table3[Country ]=[won country]&&Table3[Status]="DRAW"&&Table3[Date ]<TODAY()-50))+0`
`WIN = CALCULATE(COUNTA(Table3[Country ]),FILTER(ALLSELECTED(Table3),Table3[Country ]=[won country]&&Table3[Status]="WIN"))+0`
`LOST = CALCULATE(COUNTA(Table3[Country ]),FILTER(ALLSELECTED(Table3),Table3[Country ]=[won country]&&Table3[Status]="LOST"))+0`
`WIN% = DIVIDE([WIN],[WIN]+[LOST])`
`More than 50days win% = DIVIDE([WIN],[WIN]+[LOST]+[More than 50days DRAW])`

Result: and here is pbix file, please try it.

Best Regards,

Lin

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