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.
This my table and respective fields. I would like to create a column called "Availability" which will look at the "STAT" field and determine what operation to perform. If STAT = I0099 or E0002 then blank otherwise take the item in the DIF field and divide it over the figures in the DIF field for STAT I0099 and E0002 (2824-98). For example on the 3rd row the field should be (52/(2824-98)
Solved! Go to Solution.
Hi @erikah06
You want this done per OBNJR? I will assume so. Try this for your new calculated column, where Table1 is the table you show:
Availability = IF ( NOT Table1[STAT] IN { "I0099", "E0002" }, DIVIDE ( Table1[DIF], CALCULATE ( DISTINCT ( Table1[DIF] ), Table1[STAT] = "I0099", ALLEXCEPT ( Table1, Table1[OBJNR] ) ) - CALCULATE ( DISTINCT ( Table1[DIF] ), Table1[STAT] = "E0002", ALLEXCEPT ( Table1, Table1[OBJNR] ) ) ) )
On a general note, please always show your sample data in text-tabular format in addition to (or instead of) the screen captures. That allows people trying to help to readily copy the data and increases the likelihood of your question being answered. Just use 'Copy table' in Power BI and paste it here. Or, ideally, share the pbix (beware of confidential data).
If you had provided the data in text format, I could have copied easily and run a pretty quick test to make sure the code works as expected.
Cheers
Hi @erikah06
You want this done per OBNJR? I will assume so. Try this for your new calculated column, where Table1 is the table you show:
Availability = IF ( NOT Table1[STAT] IN { "I0099", "E0002" }, DIVIDE ( Table1[DIF], CALCULATE ( DISTINCT ( Table1[DIF] ), Table1[STAT] = "I0099", ALLEXCEPT ( Table1, Table1[OBJNR] ) ) - CALCULATE ( DISTINCT ( Table1[DIF] ), Table1[STAT] = "E0002", ALLEXCEPT ( Table1, Table1[OBJNR] ) ) ) )
On a general note, please always show your sample data in text-tabular format in addition to (or instead of) the screen captures. That allows people trying to help to readily copy the data and increases the likelihood of your question being answered. Just use 'Copy table' in Power BI and paste it here. Or, ideally, share the pbix (beware of confidential data).
If you had provided the data in text format, I could have copied easily and run a pretty quick test to make sure the code works as expected.
Cheers
Hi @AlB this works perfectly, thank you so much. Sorry about not including script. Will make sure to do next time. Perfection, see below. 🙂
CountOBJNRYearModelSTATTXT30DIFEquipmentAvailability
1 | IE000000000010003137 | 2011 | Orion Hybrid 40' | I0099 | Available | 2824 | 10003137 | |
2 | IE000000000010003137 | 2011 | Orion Hybrid 40' | E0002 | Awaiting Acceptance | 98 | 10003137 | |
4 | IE000000000010003137 | 2011 | Orion Hybrid 40' | E0013 | Non Operational Parts | 52 | 10003137 | 2% |
4 | IE000000000010003137 | 2011 | Orion Hybrid 40' | E0019 | Operational Backorder | 405 | 10003137 | 15% |
7 | IE000000000010003137 | 2011 | Orion Hybrid 40' | E0004 | Operational | 2269 | 10003137 | 83% |
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 |
---|---|
117 | |
104 | |
77 | |
73 | |
52 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |