DAX code for % scores with multiple variables and filters

[DBNN]

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 🙂

orderline

plant

country

product

T metric score

F metric score

IFAT metric score

orderline qty L

UNIT value usd

ABCD class

packaging

order ref

category

orderline

3093X

BE

Q

OK

OK

OK

5

100

B

pack

12345

m

orderline

3093X

BE

V

OK

NOT OK

NOT OK

100

23

B

bulk

12345

m

orderline

3093X

BE

BB

OK

OK

OK

3

55

B

pack

12345

m

orderline

4000C

ES

O

NOT OK

OK

NOT OK

7

45

C

pack

55555

i

orderline

4000C

ES

G

OK

OK

OK

7

100

C

pack

55555

i

orderline

3025G

GE

N

NOT OK

NOT OK

NOT OK

9

122

D

pack

32145

z

orderline

3025G

GE

M

OK

OK

OK

9

52

pack

32145

z

orderline

3093X

BE

J

OK

OK

OK

7

14

A

pack

68977

orderline

3093X

BE

J

OK

OK

OK

6

14

A

pack

67896

m

orderline

3093X

BE

O

OK

OK

OK

6

45

C

pack

67896

m

orderline

3093X

BE

G

OK

OK

OK

12

100

C

pack

67896

m

orderline

4000C

ES

Y

NOT OK

NOT OK

NOT OK

45

99

D

bulk

14785

z

orderline

4000C

ES

BB

NOT OK

OK

NOT OK

8

55

B

pack

25896

m

orderline

4000C

ES

F

OK

OK

OK

9

87

B

bulk

m

orderline

3025G

GE

G

OK

NOT OK

NOT OK

1

100

C

pack

35646

a

orderline

3025G

GE

G

OK

OK

OK

4

100

C

pack

36985

m

orderline

3025G

GE

V

OK

OK

OK

55

23

B

bulk

36985

m

orderline

3025G

GE

J

OK

OK

OK

7

14

A

pack

36985

m

orderline

3093X

BE

J

OK

OK

OK

8

14

A

pack

88995

a

[DBNN]

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

[AlB]

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