Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply

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?

5 REPLIES 5
BA_Pete
Super User
Super User

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



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




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?

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



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




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
Super User
Super User

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

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.