cancel
Showing results for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

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.

Announcements

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors