Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
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 |
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:
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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
43 | |
21 | |
20 | |
15 | |
13 |
User | Count |
---|---|
45 | |
41 | |
39 | |
19 | |
19 |