Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors