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,
WE are trying to track how much work is contracted out. All numbers that start with 1 in STEP 1, 2, 3 belong to our company everything us else is contracted out. We would like to count everything that does not start with 1 so it can be shown on a card.
STATUS | TRIP | STEP 1 | STEP 2 | STEP 3 | TOTAL ? |
Complete | P26332 | 126 | 126 | GO928 | |
Complete | P26321 | 126 | 126 | TRACT | |
complete | D84470 | 101 | 101 | DFG | |
Complete | D84416 | 114 | 107 | SDF | |
Complete | D84457 | 116 | 108 | 132 | |
complete | D84523 | 121 | 123 | 234 | |
complete | D84557 | 109 | 109 | 123 | |
complete | D84405 | 120 | TRACT | 874 | |
complete | D84505 | GO123 | 102 | ||
complete | P26345 | 110 | 110 | ||
complete | D84534 | 108 | 108 | ||
complete | D84533 | 118 | 118 | 234 | |
Complete | P26344 | 107 | 107 | 534 | |
Complete | P26318 | 101 | 101 | 123 | |
complete | P26343 | 429 | 429 | 133 | |
complete | P26313 | 124 | 124 | ||
complete | P26340 | 114 | 114 | 456 | |
complete | P26341 | 108 | 108 | 123 |
Solved! Go to Solution.
Then you should add that condition to the formula, it would look something like:
TOTAL = IF(ISBLANK(STEP1)=TRUE,0,IF(LEFT(STEP1,1)="1",0,1))+IF(ISBLANK(STEP2)=TRUE,0,IF(LEFT(STEP2,1)="1",0,1))+IF(ISBLANK(STEP3)=TRUE,0,IF(LEFT(STEP3,1)="1",0,1))
Let me know if it works
I'm not sure if I follow, but I think you can calculate that as a column by means of a formula like: TOTAL = IF(LEFT(STEP1,1)="1",0,1)+IF(LEFT(STEP2,1)="1",0,1)+IF(LEFT(STEP3,1)="1",0,1)
This will give you the sum in the total field of subcontracted steps per trip
Hey @alexvc this works well but I forgot to ask can we have blanks not be included in the sum but rather be treated as our drivers who start with 1. I tried to change it myself but failed.
Then you should add that condition to the formula, it would look something like:
TOTAL = IF(ISBLANK(STEP1)=TRUE,0,IF(LEFT(STEP1,1)="1",0,1))+IF(ISBLANK(STEP2)=TRUE,0,IF(LEFT(STEP2,1)="1",0,1))+IF(ISBLANK(STEP3)=TRUE,0,IF(LEFT(STEP3,1)="1",0,1))
Let me know if it works
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 |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
129 | |
106 | |
106 | |
86 | |
72 |