Multiplying the Value of Two Cards and then Adding this to the Value of a Third Card

[Richard_Thomas]

Hello

I am trying to multiple the values of two cards to create an additonal card with the value of this sum in it. I want to multiply the value in the RRAP Referrals In Progress card (currently 298) by the value in the Average Cost Per Referral card (currently £193). See below:

I've tried to create a measure using DAX using the following:

Estimated Cost of Referrals In Progress = VALUE(DataCardValue.RRAPReferralsInProgress)*VALUE(DataCardValue. AverageCostPerReferral)

But this doesn't work. 

Once I've got it to work, I want to use this new calculated value to create an additional card called 'Estimated Cost of Referrals In Progress'. I then want to be able to add this value to the value of the Total RRAP Expenditure card and use this value to create another card to give me the total of the existing expenditure plus the estimated expenditure. This will then be a great tool to use for budgeting purposes.

Any help here would be really appreciated. Thanks in anticipation

Kind regards

Richard

[whitch · Accepted solution]

Try this:

New Measure = CALCULATE(Referral[Average Cost Per Referral for RRAP], Referral[DateReferralCompleted].[Date] >= DATE(2023,4,1)) 

[Richard_Thomas]

Hi @whitch 

That works! I was able to use the CALCULATE function to apply the filters to other measures and have been able to combine these to get the output I want. 

Thank you ever so much, I really appreciate the input

Kind regards

Richard

[whitch]

I would assume that the first two cards you have are actually showing the values of two existing measures, [RRAPReferralsInProgress] and [AverageCostPerReferral].  If they're someyhow not this, make those measures.  Then you can have:

Estimated Cost of Referrals In Progress = [RRAPReferralsInProgress] * [AverageCostPerReferral]

In general, measures should refer to "the measure the card is showing" not "the value in the card".

[Richard_Thomas]

Thanks @whitch for the response. I've previously tried this calculation but unfortunately it doesn't calculate the right answer. This is because the measure have different filters which do not work when used together.  

Thanks again for your input

Kind regards

Richard

[whitch]

In that case you can use the CALCULATE function to apply the different filters to each measure.  I don't know what filters you want, but this is roughly how it goes:

Estimated Cost of Referrals In Progress = CALCULATE([RRAPReferralsInProgress], 'tablename'[ColumnNameA] > 0) * CALCULATE([AverageCostPerReferral], 'tablename'[ColumnNameB] < 0)

[Richard_Thomas]

Thanks @whitch, am starting to get somewhere with this approach. One query:

When I previously used a filter on the measure, I've selected a date range of on or after 01/04/2023 to on or before 31/03/2024. This is the financial year range we use. This returned the Average Value as £193.62 which I know is correct. 

I'm trying to replicate that filter. However, when I use the following DAX expression:

New Measure = CALCULATE(Referral[Average Cost Per Referral for RRAP], DATESYTD(Referral[DateReferralCompleted].[Date])) 

The returned the Average Value is now £191.10. I suspect this is because it is calculating YTD from the period 01/01/2023 (not 01/04/2023). 

Any suggestions how I can amend the New Measure to calculate the YTD from the 01/04/2023?

Thanks in anticipation

Richard