Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Generate Spread between Slicer Selected Values: Calculations operate on both lines seperately

I have two measures created which correctly pull the two selected columns individually, which are as follows:
 
Filter First Security =
CALCULATE(
Sum('First Data'[Value]),
Filter(
Rates,
Rates[Security] IN {SELECTEDVALUE('Second Selection'[Security])}))
 
Filter Second Security =
CALCULATE(
Sum('First Data'[Value]),
FILTER(
Rates,
Rates[Security] IN {SELECTEDVALUE('First Selection'[Security])}))

I've got a third measure, which is meant to find the difference between the values of the two measures, but for some reason, it keeps generating the two above measures seperately, with the subtract operation just giving an inverted line. That measure is as follows:
 
Calculated Spread =
CALCULATE(
(value([Filter First Security])-Value([Filter Second Security])),
Filter('First Data', [Value]))
 
Heres a photo of the dashboard, as well, if that helps to understand what im trying to output. 

 

Thanks for any help you all can gather!



  • v-easonf-msft's avatar
    v-easonf-msft
    4 years ago

    Hi, Anonymous 

     

    Calculated Spread =[Filter First Security])-[Filter Second Security])

     

    If the above measure doesn't work, you can try formula as below:

     

    Calculated Spread =
VAR tab =
    SUMMARIZE (
        'First Data',
        'First Data'[Date],
        //'First Data'[Security],
        "measure1", [Filter First Security],
        "measure2", [Filter Second Security]
    )
RETURN
    SUMX ( tab, [measure1] - [measure2] )

     

    If it doesn't work, please share a sample file for further research.

    Best Regards,
    Community Support Team _ Eason

     

4 Replies

  • ValtteriN's avatar
    ValtteriN
    Community Champion
    4 years ago

    Hi,

    Typically difference measures should work simpy by subtracting the first measue from the second one. So a simple Calculate spread = [Filter First Security]-[Filter Second Security]. Is there a reason this doesn't work in your example?

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Thats what i thought as well, so you see my confusion. The measures are just filtering for the selected values, so i wouldnt think that would cause an issue, but it seems to want to register them seperately as opposed to combining them. I could try removing the calculate function and see if thats the issue, but otherwise, im lost as to why it doesnt want to simply subtract the values

      • v-easonf-msft's avatar
        v-easonf-msft
        Community Support
        4 years ago

        Hi, Anonymous 

         

        Calculated Spread =[Filter First Security])-[Filter Second Security])

         

        If the above measure doesn't work, you can try formula as below:

         

        Calculated Spread =
VAR tab =
    SUMMARIZE (
        'First Data',
        'First Data'[Date],
        //'First Data'[Security],
        "measure1", [Filter First Security],
        "measure2", [Filter Second Security]
    )
RETURN
    SUMX ( tab, [measure1] - [measure2] )

         

        If it doesn't work, please share a sample file for further research.

        Best Regards,
        Community Support Team _ Eason

         

  • v-easonf-msft's avatar
    v-easonf-msft
    Community Support
    4 years ago

    Hi, Anonymous 

    Can you tell me if your problem is solved?

    If it doesn't work, please a sample file for further research.

     

    Best Regards,
    Community Support Team _ Eason