Forum Discussion

hashtag_pete's avatar
hashtag_pete
Helper V
4 years ago
Solved

Divide two Calculation Items

Hello community, 

 

I have a calculation group where I have a couple of calculation items, which perform a SUMX of a measure SUM(kg) and a referenced table with several columns, where I have the prices at a certain implementation date, e.g.

Jan21:
SUMX (
    'BX_Table',
    SELECTEDMEASURE()
        * RELATED ( 'VRP Prices'[01.01.2021] )
)


Jun21:
SUMX (
    'BX_Table',
    SELECTEDMEASURE()
        * RELATED ( 'VRP Prices'[01.06.2021] )
)

via a slicer on the calculation group, the user can chose the price applied to the SELECTEDMEASURE(kg) he wants to see. That works fine. 

What I want to achieve now, is a DIVIDE or subtraction when two Calculation Items are chosen. I wouldn't mind if the formular breaks when more then two Calculation Items are chosen - in the first instance, I just want to show the % between the value June vs January.

My first attempt was at reading out the Calculation items used, but this does not seem to work. Has anyone an idea?

 

Thanks a lot!

Need Help
  • johnt75's avatar
    johnt75
    4 years ago

    I think I got the syntax for TREATAS wrong, try using VALUES( CalcGroupSlicer1[Name])

3 Replies

  • johnt75's avatar
    johnt75
    Super User
    4 years ago

    You could create 2 new copies of your calculation group table just for use on the slicers.

    Calc slicer 1 = VALUES('Calculation Group')
    Calc slicer 2 = VALUES('Calculation Group')

    Do not create any relationships from these tables, leave them totally disconnected.

    You can then create measure to perform your division or subtraction or whatever like

    Comparison measure =
    var firstValue = CALCULATE( [Base measure], TREATAS( 'Calc slicer 1'[Calc item], 'Calculation Group'[Calc item]) )
    var secondValue = CALCULATE( [Base measure], TREATAS( 'Calc slicer 2'[Calc item], 'Calculation Group'[Calc item]) )
    return DIVIDE( firstValue, secondValue )
  • hashtag_pete's avatar
    hashtag_pete
    Helper V
    4 years ago

    Hello johnt75 
    thanks for your suggestion, however it won't work for me. I tried several things, but maybe I am misinterpreting your formular:

    In the comparison measure, the TREATAS function always gives me an error. Is the Expression missing?

    I have as a first snippet:

    Comparison Measure = 
    VAR firstValue = 
        CALCULATE(
            [Summe Gewicht kg],  //this is my base measure, also used for Calculation Group
            TREATAS(CalcGroupSlicer1[Name], ImplementaionDateSelection[Name]))  //first element is underlined red, and following the syntax an expression is needed here. However, I cannot insert SELECTEDVALUE(CalcGroupSlicer1[Name]) so I don't know what expression is needed.

     looking at your formular, it looks like you have two columns in your TREATAS, but this throws an error?

    • johnt75's avatar
      johnt75
      Super User
      4 years ago

      I think I got the syntax for TREATAS wrong, try using VALUES( CalcGroupSlicer1[Name])