Forum Discussion

AN2021's avatar
AN2021
Frequent Visitor
5 years ago
Solved

Substract column with a constant from another column

Hi, I want to calculate as follow: Minus "Value" for each bucket with "Value" from bucket 1 group by "Division" Division Bucket Value Dif A 1 7 0 (7 - 7) A 2 8 1 (8 - 7) A 3...
Need Help
  • Fowmy's avatar
    Fowmy
    5 years ago

    AN2021 

    Try this code please:

    Difference = 
Table3[Value]
-
CALCULATE(
    sum(Table3[Value]),
    Table3[Bucket] = 1,
    ALLEXCEPT(Table3,Table3[Division])
)
  • themistoklis's avatar
    themistoklis
    5 years ago

    AN2021 

     

    It still works with new dimensions.

    See attached file.

     

    If you could share you file so as to have a look at it that would be great

     

     

  • AN2021's avatar
    AN2021
    5 years ago

    Oh my bad, yeap its work, thanks

Fowmy's avatar
Fowmy
Icon for Super User rankSuper User
5 years ago

AN2021 

Add the following column:

Dif = Table3[Value] - CALCULATE( MIN(Table3[Value]), ALLEXCEPT(Table3,Table3[Division]))

 



  • AN2021's avatar
    AN2021
    Frequent Visitor
    5 years ago

    Hi Fowmy

     

    Actually I want to get the "Value" from Bucket = 1, not the Min of "Value". Your solution might work with my example, but in my actual case some "Value" in Bucket = 1 is not the smallest one.

    • Fowmy's avatar
      Fowmy
      Icon for Super User rankSuper User
      5 years ago

      AN2021 

      Try this code please:

      Difference = 
Table3[Value]
-
CALCULATE(
    sum(Table3[Value]),
    Table3[Bucket] = 1,
    ALLEXCEPT(Table3,Table3[Division])
)