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

DAX code for % scores with multiple variables and filters

Hello

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
orderline3093XBEQOKOKOK5100Bpack12345m
orderline3093XBEVOKNOT OKNOT OK10023Bbulk12345m
orderline3093XBEBBOKOKOK355Bpack12345m
orderline4000CESONOT OKOKNOT OK745Cpack55555i
orderline4000CESGOKOKOK7100Cpack55555i
orderline3025GGENNOT OKNOT OKNOT OK9122Dpack32145z
orderline3025GGEMOKOKOK952 pack32145z
orderline3093XBEJOKOKOK714Apack68977 
orderline3093XBEJOKOKOK614Apack67896m
orderline3093XBEOOKOKOK645Cpack67896m
orderline3093XBEGOKOKOK12100Cpack67896m
orderline4000CESYNOT OKNOT OKNOT OK4599Dbulk14785z
orderline4000CESBBNOT OKOKNOT OK855Bpack25896m
orderline4000CESFOKOKOK987Bbulk m
orderline3025GGEGOKNOT OKNOT OK1100Cpack35646a
orderline3025GGEGOKOKOK4100Cpack36985m
orderline3025GGEVOKOKOK5523Bbulk36985m
orderline3025GGEJOKOKOK714Apack36985m
orderline3093XBEJOKOKOK814Apack88995a
2 REPLIES 2
Frequent Visitor

Hi,

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])
  • FINAL SCORE IFAT = [YES SCORE]/([YES SCORE]+[NO SCORE])

 

 

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

Hi @DBNN 

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.

Cheers 

 

SU18_powerbi_badge

 

Helpful resources

Announcements
secondImage

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

secondImage

Power BI Women

Join our monthly meetings and learning sessions.

secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

secondImage

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.