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 have a calculated column called [Availability] thanks to @AlB I am attempting to create a new column or measure, not sure which one makes more sense. The goal is to have a subsequent column/measure that sums the [Availability] column (%) of those items in the [STAT] column that meet certain criteria = "E0002", "E0003", "E0005", E00019" and sums those fields for each equipment and produces the measure/column called Operational.
Count | OBJNR | Equipment | Year | Technical obj. Type | STAT | TXT30 | DIF | PLANT NAME | Tech Obj Type Name | Class | Availability |
2 | IE000000000010000575 | 10000575 | 2001 | 1 | E0002 | Awaiting Acceptance | 12 | POLK | New Flyer 40' | TRANSIT | |
2 | IE000000000010000575 | 10000575 | 2001 | 1 | E0009 | Off Site Vendor | 29 | POLK | New Flyer 40' | TRANSIT | 0.49% |
2 | IE000000000010000575 | 10000575 | 2001 | 1 | E0010 | Off Site Leased | 0 | POLK | New Flyer 40' | TRANSIT | 0.00% |
1 | IE000000000010000575 | 10000575 | 2001 | 1 | E0014 | Non Operational Reserve Fleet | 4120 | POLK | New Flyer 40' | TRANSIT | 69.42% |
1 | IE000000000010000575 | 10000575 | 2001 | 1 | I0099 | Available | 5947 | POLK | New Flyer 40' | TRANSIT | |
7 | IE000000000010000575 | 10000575 | 2001 | 1 | E0019 | Operational Backorder | 241 | POLK | New Flyer 40' | TRANSIT | 4.06% |
6 | IE000000000010000575 | 10000575 | 2001 | 1 | E0005 | Operational Open Defect | 1158 | POLK | New Flyer 40' | TRANSIT | 19.51% |
10 | IE000000000010000575 | 10000575 | 2001 | 1 | E0008 | Off Site Central Shops | 99 | POLK | New Flyer 40' | TRANSIT | 1.67% |
24 | IE000000000010000575 | 10000575 | 2001 | 1 | E0013 | Non Operational Parts | 33 | POLK | New Flyer 40' | TRANSIT | 0.56% |
32 | IE000000000010000575 | 10000575 | 2001 | 1 | E0004 | Operational | 4375 | POLK | New Flyer 40' | TRANSIT | 73.72% |
Solved! Go to Solution.
I looks like it might be easiest to add a calculated column that captures operational or not.
Classification = IF ( LEFT ( Table1[TXT30] , 11 ) = "Operational" , "Operational" , Table1[TXT30] )
Then write a measure to sum the Availability
Total Availability = SUM( Table1[Availability] )
Then you can pull the Classification column and [Total Availability] into a report along with whatever other fields you need.
You seem to make contradictory statements. In the explanation you say:
If [STAT] ="E0019" or "E0005" then sum these fields with "E0004" else if [STAT] does not equal any of these designated [STAT] results then [Availability]
but then in the example you do the sum when [STAT] = "E0004" and leave blank the rows where [STAT] ="E0019" or "E0005" (that is neither the sum nor [Availability]).
I will assume that what you show in the example is what you really want. If so, try the following for the new calculated column:
Operational = IF ( NOT Table1[STAT] IN { "E0019"; "E0005" }; IF ( Table1[STAT] = "E0004"; Table1[Availability] + CALCULATE ( DISTINCT ( Table1[Availability] ); Table1[STAT] = "E0019"; ALLEXCEPT ( Table1; Table1[OBJNR] ) ) + CALCULATE ( DISTINCT ( Table1[Availability] ); Table1[STAT] = "E0005"; ALLEXCEPT ( Table1; Table1[OBJNR] ) ); Table1[Availability] ) )
If you don't want to return blank where [STAT] ="E0019" or "E0005" but [Availability] instead, you can use this (different from what you show on the example table):
Operational2 = IF ( Table1[STAT] = "E0004"; Table1[Availability] + CALCULATE ( DISTINCT ( Table1[Availability] ); Table1[STAT] = "E0019"; ALLEXCEPT ( Table1; Table1[OBJNR] ) ) + CALCULATE ( DISTINCT ( Table1[Availability] ); Table1[STAT] = "E0005"; ALLEXCEPT ( Table1; Table1[OBJNR] ) ); Table1[Availability] )
Regarding whether a measure would be better, I would need to understand, conceptually, what you ultimately want to do with the data and what/how you want to show as result.
Hi @erikah06
I'm afraid you'll have to explain a bit more what it is that you need and how you'd like to display it.
Can you provide an example based on your sample data, explaining how the [STAT] and [Equipment] fields would affect the result?
Cheers
Hi @AlB sorry for ambiguity. So, this is the hopeful outcome of the [Operational] field. If [STAT] ="E0019" or "E0005" then sum these fields with "E0004" else if [STAT] does not equal any of these designated [STAT] results then [Availability]
Count | OBJNR | Equipment | Year | Technical obj. Type | STAT | TXT30 | DIF | PLANT NAME | Tech Obj Type Name | Class | Availability | Operational |
2 | IE000000000010000575 | 10000575 | 2001 | 1 | E0002 | Awaiting Acceptance | 12 | POLK | New Flyer 40' | TRANSIT | ||
2 | IE000000000010000575 | 10000575 | 2001 | 1 | E0009 | Off Site Vendor | 29 | POLK | New Flyer 40' | TRANSIT | 0.49% | =0.49% |
2 | IE000000000010000575 | 10000575 | 2001 | 1 | E0010 | Off Site Leased | 0 | POLK | New Flyer 40' | TRANSIT | 0.00% | =0.00% |
1 | IE000000000010000575 | 10000575 | 2001 | 1 | E0014 | Non Operational Reserve Fleet | 4120 | POLK | New Flyer 40' | TRANSIT | 69.42% | =69.42% |
1 | IE000000000010000575 | 10000575 | 2001 | 1 | I0099 | Available | 5947 | POLK | New Flyer 40' | TRANSIT | ||
7 | IE000000000010000575 | 10000575 | 2001 | 1 | E0019 | Operational Backorder | 241 | POLK | New Flyer 40' | TRANSIT | 4.06% | |
6 | IE000000000010000575 | 10000575 | 2001 | 1 | E0005 | Operational Open Defect | 1158 | POLK | New Flyer 40' | TRANSIT | 19.51% | |
10 | IE000000000010000575 | 10000575 | 2001 | 1 | E0008 | Off Site Central Shops | 99 | POLK | New Flyer 40' | TRANSIT | 1.67% | =1.67% |
24 | IE000000000010000575 | 10000575 | 2001 | 1 | E0013 | Non Operational Parts | 33 | POLK | New Flyer 40' | TRANSIT | 0.56% | =0.56% |
32 | IE000000000010000575 | 10000575 | 2001 | 1 | E0004 | Operational | 4375 | POLK | New Flyer 40' | TRANSIT | 73.72% | =73.72%+19.51%+4.06% |
I looks like it might be easiest to add a calculated column that captures operational or not.
Classification = IF ( LEFT ( Table1[TXT30] , 11 ) = "Operational" , "Operational" , Table1[TXT30] )
Then write a measure to sum the Availability
Total Availability = SUM( Table1[Availability] )
Then you can pull the Classification column and [Total Availability] into a report along with whatever other fields you need.
You seem to make contradictory statements. In the explanation you say:
If [STAT] ="E0019" or "E0005" then sum these fields with "E0004" else if [STAT] does not equal any of these designated [STAT] results then [Availability]
but then in the example you do the sum when [STAT] = "E0004" and leave blank the rows where [STAT] ="E0019" or "E0005" (that is neither the sum nor [Availability]).
I will assume that what you show in the example is what you really want. If so, try the following for the new calculated column:
Operational = IF ( NOT Table1[STAT] IN { "E0019"; "E0005" }; IF ( Table1[STAT] = "E0004"; Table1[Availability] + CALCULATE ( DISTINCT ( Table1[Availability] ); Table1[STAT] = "E0019"; ALLEXCEPT ( Table1; Table1[OBJNR] ) ) + CALCULATE ( DISTINCT ( Table1[Availability] ); Table1[STAT] = "E0005"; ALLEXCEPT ( Table1; Table1[OBJNR] ) ); Table1[Availability] ) )
If you don't want to return blank where [STAT] ="E0019" or "E0005" but [Availability] instead, you can use this (different from what you show on the example table):
Operational2 = IF ( Table1[STAT] = "E0004"; Table1[Availability] + CALCULATE ( DISTINCT ( Table1[Availability] ); Table1[STAT] = "E0019"; ALLEXCEPT ( Table1; Table1[OBJNR] ) ) + CALCULATE ( DISTINCT ( Table1[Availability] ); Table1[STAT] = "E0005"; ALLEXCEPT ( Table1; Table1[OBJNR] ) ); Table1[Availability] )
Regarding whether a measure would be better, I would need to understand, conceptually, what you ultimately want to do with the data and what/how you want to show as result.
Again @AlB sorry for confusion, the first solution is what I was intending to relay. I needed to add 2 more [STAT] codes to your solution and viola, I had all 4 of the [STAT] codes necessary to portray an Operational status. Thank you. I already accepted @jdbuchanan71 as a Solution. I see you have been on here the longest out of the 3 of us @AlB, do you know if I can accept 2 solutions to the problem? Thank you
Oh, no worries.
Sure, you can mark as many posts as you want as solutions, no problem. Just like you marked the first one, clicking on "Accept as solution". You can also "unmark" a solution if for instance you make a mistake identifying it. To do this, click on the ellipsis on the top right corner of the message and choose "Not the solution"
Thanks @AlB that was very helpful. You have a great day. Marked your response as a solution as well. 🙂
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 |
---|---|
107 | |
105 | |
88 | |
74 | |
66 |
User | Count |
---|---|
126 | |
111 | |
100 | |
83 | |
71 |