Forum Discussion

Singh_Yoshi's avatar
Singh_Yoshi
Helper I
1 year ago
Solved

DAX Code not working

I have following table in BI. 

ItemCost
18" Alloy-23000
20" Alloy31000
Air Bag5000
Deatc250

 

I want to subtract 18" Alloy cost(-23000) from 20" Alloy(31000) and remove 18" Alloy from the table. I Used following Dax function and it returns above table as out without doing any subtraction. Please correct my code or help me with new code. Thank you in advance.
Used DAX:

 

IF (
[Item] = "20"" Alloy",
[Costs] +
CALCULATE (
SUM([Costs]),
[Item] = "18" Alloy"
),
FJ11_16[Calculated Costs]
)

Desired Output:

 

ItemsCost
20" Alloy8000
Air Bag5000
Deatc250
Need Help
  • danextian's avatar
    danextian
    1 year ago

    Hi Singh_Yoshi 

     

    Please try this:

    Measure1 = 
VAR _alloy =
    CALCULATE (
        SUM ( SampleData[Cost] ),
        FILTER (
            ALL ( SampleData[Item], SampleData[Cost] ),
            SampleData[Item] IN { "18"" Alloy", "20"" Alloy" }
        )
    )
RETURN
    SWITCH (
        TRUE (),
        SELECTEDVALUE ( SampleData[Item] ) = "18"" Alloy", BLANK (),
        SELECTEDVALUE ( SampleData[Item] ) = "20"" Alloy", _alloy,
        SUM ( SampleData[Cost] )
    )

     

3 Replies

  • danextian's avatar
    danextian
    Super User
    1 year ago

    Hi Singh_Yoshi 

     

    Please try this:

    Measure1 = 
VAR _alloy =
    CALCULATE (
        SUM ( SampleData[Cost] ),
        FILTER (
            ALL ( SampleData[Item], SampleData[Cost] ),
            SampleData[Item] IN { "18"" Alloy", "20"" Alloy" }
        )
    )
RETURN
    SWITCH (
        TRUE (),
        SELECTEDVALUE ( SampleData[Item] ) = "18"" Alloy", BLANK (),
        SELECTEDVALUE ( SampleData[Item] ) = "20"" Alloy", _alloy,
        SUM ( SampleData[Cost] )
    )

     

    • Singh_Yoshi's avatar
      Singh_Yoshi
      Helper I
      1 year ago

      I have a following table called Table1,

      Model CodeMSRPLC
      FJ111392000 
      FJ161580000188000
      FJ71762000182000
      FJ81912000150000
      FJ15193700025000
      FJ122279000342000

       

      I have few measures as below names and consists of Items and Cost

      • FJ11-16 - Sum of the measure is (-1674)
      • FJ16-7 - Sum of the measure is (70650)
      • FJ7-8 - Sum of the measure is (176150)
      • FJ8-15 - Sum of the measure is (-68150)
      • FJ15-12 - Sum of the measure is (28500)

      1. Now I want to subtract LC cost - SUM of each measure

      2. I need the percentage of (LC Cost - Sum of each measure)

       

      Kindly help me with DAX code