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 within the DAX in order for this to work properly but I am new to PowerBi and am not au fait with the filter!

This is what I want my measure to look like:

Potentially converting = (Total of Estimated value (filtered by Status = won, and filtered by Closed Date = this month)) PLUS (Total of Estimated Value (filtered by Temperature = hot))
I have a date table already set up to with Month and Year formatting. Please can you help me?

Need Help

5 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    LauraCawdronDav , Not very clear.

     

    If date slicer is joined to table or from table column, that will filter it

    And a measure like

    calculate([Total of Estimated value], filter(Table, Table[Status] ="Won" && Table[Temperature] ="Hot"))

  • 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