Showing results for 
Search instead for 
Did you mean: 
Frequent Visitor

DAX code for % scores with multiple variables and filters


I think I have a bit of a challenge. At least for me it is 🙂. But hopefully one of you can find the cure to some of my recent sleepless nights.

I have a fairly large dbase in excel in which I upload automatic reporting from a server. After some data crunching the dbase is moving to Power BI. In excel I have no problem handling the data, but in Power BI I seem to be stuck on the simplest of things. Calculating % with multiple variables and filters is my problem. I can't code it properly and I wonder if DAX can clear my problem completely. I hope you can help.


I have added a table below which is a simple example for educational purposes. The goal would be to calculate the IFAT metric score in % using DAX.

  • filters: category m and blank and z only
  • filters: results must be shown% of the total and of the individual countries
  • filters: ABCD take out the D-items. we do not need their score
  • an OK IFAT score comes from both OK T-metric and F-metric scores. If either of these is not ok, the IFAT score is not ok too
  • the IFAT score in% comes from the OK score divided by the total sum of all metric scores but per grouping of order reference (see next point)
  • the IFAT score% outcome score must be viewed per total order ref (i.e. order ref 12345 has 3 order lines, but will score per what the total will look like per order ref 12345. in this case the score will not be ok if an orderline is not ok on the F metric


In the end I will need the score in an advanced card visual that shows me the IFAT metric score in% every time I use a variable such as country or plant or package or category
My biggest challenge is how to calculate % in a dbase where it is difficult to calculate a total due to the many variables and filter requirements. There is a lot of noise in the dbase and I don't know if DAX can go around that.


Thanks for ‌‌ feedback 🙂


orderlineplantcountryproductT metric scoreF metric scoreIFAT metric scoreorderline qty LUNIT value usdABCD classpackagingorder refcategory
orderline3093XBEVOKNOT OKNOT OK10023Bbulk12345m
orderline4000CESONOT OKOKNOT OK745Cpack55555i
orderline3025GGENNOT OKNOT OKNOT OK9122Dpack32145z
orderline3025GGEMOKOKOK952 pack32145z
orderline4000CESYNOT OKNOT OKNOT OK4599Dbulk14785z
orderline4000CESBBNOT OKOKNOT OK855Bpack25896m
orderline4000CESFOKOKOK987Bbulk m
orderline3025GGEGOKNOT OKNOT OK1100Cpack35646a
Frequent Visitor


Thanks for the quick reply.  Much appreciated .  Below is the task and how far I got with it :


Purpose: display the % IFAT score by country on the basis of metric scores results F and T (already calculated), for the number of order refs in the total table. Taking into account that the IFAT metric score is only valid for category values "m" and "blank" and "z", and never for ABCD class "D".  If the score is not ok for 1 line in the order ref the total order ref score is not ok.


So I created some 5 new columns :

1) ID IFAT = IF(orderlinedb[IFAT metric score]="OK",1,0)

This to move the "OK" and "NOT OK" scores to a value 1 or 0


2) Total IFAT =

CALCULATE (sum(orderlinedb[ID IFAT]),

    FILTER ( orderlinedb, orderlinedb[category] IN { "m", "","z" } ),

    FILTER ( orderlinedb, orderlinedb[country] IN { "BE", "ES","GE" } ),

    FILTER (orderlinedb, orderlinedb[ABCD class]<>"D")

)/DISTINCTCOUNT(orderlinedb[order ref])


This to filter out to the core data from the total dbase.  Only that data will be the total sum or denominator for the % score


3)  ID fail order ref = if(orderlinedb[ID IFAT]=0,orderlinedb[order ref],0)

This to know which ordernumbers failed


4) YES ID IFAT = if(LOOKUPVALUE ( [Total IFAT], orderlinedb[ID fail order ref], orderlinedb[order ref] )>0,0,1)

The nominator.    At least I hoped so


5) NO ID IFAT = if(orderlinedb[YES ID IFAT]=0,1,0)

All the failed ones


Then I created 3 measure:

  • YES SCORE = sum(orderlinedb[YES ID IFAT])
  • NO SCORE = sum(orderlinedb[NO ID IFAT])



in the hope to find the %...     but I stranded at 63%

IFAT % score by country BE… which is not correct.

Does not look really fantastic , does it.  But I tried.  Hope there is a better way 


Thanks,  Danny


Super User III
Super User III


I don't think it will be a problem to do this in DAX but I don't completely follow what you need.  Please provide an example based on the data you show, detailing the step for the calculation and the functional logic behind it. So that the requirements can be understood clearly.


Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.





Helpful resources



We are excited to announce the Power BI Super Users!

Wave Release 2

Check out the updates in Power BI.

Overview of Power BI 2020 release wave 2!

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!


The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.