cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
MYDATASTORY
Resolver I
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.

Highly appreciated for your help.

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
v-lili6-msft
Community Support
Community Support

hi, @MYDATASTORY 

First, when you load the data from excel, please choose "Table3" instead of the whole sheet.

2.JPG

 

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:

3.JPG

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.

View solution in original post

1 REPLY 1
v-lili6-msft
Community Support
Community Support

hi, @MYDATASTORY 

First, when you load the data from excel, please choose "Table3" instead of the whole sheet.

2.JPG

 

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:

3.JPG

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.

View solution in original post

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!