DAX Create Calculated Field that Performs Calculations Based on Adjacent Field

[erikah06]

Result TableThis 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)

[AlB · Accepted 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

Code formatted with  

[erikah06]

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%