Measure: adding two card values together

[LauraCawdronDav]

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?

[BA_Pete]

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]

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?

[BA_Pete]

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

[v-easonf-msft]

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

[amitchandak]

@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"))