Forum Discussion

LauraCawdronDav's avatar
LauraCawdronDav
Helper I
5 years ago

Measure: adding two card values together

Hi! I'm looking to create a measure which adds the value of two filtered cards together, I will then use the measure to conditionally format the cards. I'm assuming I need to include the filters wit...
Need Help
BA_Pete's avatar
BA_Pete
Super User
5 years ago

Hi LauraCawdronDav ,

 

Just converting your measure into DAX looks like this:

 

Potentially converting =
VAR calc1 =
CALCULATE(
    SUM(yourTable[Estimated Value]),
    FILTER(
        yourTable,
        yourTable[Status] = "won"
        && MONTH(yourTable[Closed Date]) = MONTH(TODAY())
        && YEAR(yourTable[Closed Date]) = YEAR(TODAY())
    )
)
VAR calc2 = 
CALCULATE(
    SUM(yourTable[Estimated Value]),
    yourTable[Temperature] = "hot"
)
RETURN
calc1 + calc2

 

 

Pete

  • LauraCawdronDav's avatar
    LauraCawdronDav
    Helper I
    5 years ago

    Hi Pete,

     

    Thank you for your response. I used your solution and it doesn't seem to be adding up properly. So the first filter, Calc 1 (won this month), is adding up to £970 and the second filter, calc 2 (hot opportunities this month) is £980. However, the return comes out as £319k not £195k. I noticed that calc2 didn't specify whether this opportunity was open or not. These 'hot' opportunities are still open so I edited the formula as below. NB: our 'hot' temp is classified as '"1".

     

    Potentially converting 1 =

    VAR calc1 = CALCULATE(SUM(Opportunities[Estimated Value]), FILTER(Opportunities, Opportunities[Status] = "Won" && MONTH(Opportunities[Close Date] = MONTH(TODAY()) && YEAR(Opportunities[Close Date] = YEAR(TODAY())))))

    VAR calc2 = CALCULATE(SUM(Opportunities[Estimated Value]), FILTER(Opportunities, Opportunities[Temp] = "1"),FILTER(Opportunities, Opportunities[Status] = "Open"))

    RETURN calc1+calc2

     

    Please can you tell me where I might be going wrong?

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

      Hi, LauraCawdronDav 

      I checked the formula you have provided but didn't  found any problems.

      Can you provide a sample file for  further research？

       

      Best Regards,
      Community Support Team _ Eason

    • BA_Pete's avatar
      BA_Pete
      Super User
      5 years ago

      Hi LauraCawdronDav ,

       

      Apologies for the late reply, Easter break 🙂

       

      At a glance, the formula itself seems ok, but impossible for me to say where the incorrect values are coming from without seeing your data and model to be honest.

       

      If you're able to share an example of your data I'm happy to take a look for you. Please ensure that you remove any sensitive information before sharing.

       

      Pete