How to Calculate Net Based on Two Calculated Measures

[prabhatsgautam]

Hi,

A quick question. Would it be possible for me to calculate a net value based on two calculated measures. Example:

Measure 1 = 0.50

Measure 2= -0.56

What is the DAX expression for calculating net based on the above two measures?

Thanks. Please let me know and I can explain my tables/data furthermore.

Best,

Prabhat

[KHorseman]

You can write a measure that references another measure. Such as

Measure 3 = [Measure 1] + [Measure 2]

...which in this case would yield a result of -0.06. Is that what you're looking for?

[prabhatsgautam]

Thank you. Yes, this approach does solve my problem.

I was also curious about measure on measure based calculations. What is the extent as well as limitations of measures based on measure calculations? What operators can be used and cannot be used between two or more measures to calculate a higher degree measure?

Thanks!

[KHorseman · Accepted solution]

@prabhatsgautam there really isn't any limitation as far as operators. You can add, subtract, multiply, divide, even reference a measure inside another formula or expression.

You can even do a conditional statement so that one measure is returned under some conditions and a different measure is used under other conditions. For example, imagine you have a report that lists sales reps and gives the total units sold per sales rep, but on the grand total line at the bottom, you want to see the average units sold per sales rep rather than the sum of all units sold.

Total Units = SUM(SalesTable[Units])

Avg Units per Rep = AVERAGEX(
 VALUES(Employees[SalesRepID]),
 [Total Units]
)

Units Sold = IF(
 HASONEVALUE(Employees[SalesRepID]),
 [Total Units],
 [Avg Units per Rep]
)

In fact, referencing an existing measure really ought to be one of your most common techniques. Most of my measures follow some variation of:

NewMeasure = CALCULATE(
	[OtherMeasure],
	FILTER(
		TableName,
		TableName[ColumnName] = <insert filter criteria here>
	)
)

As for limitations, the only thing I can think of is that you have to remember that each referenced measure is calculated separately. When you do [Measure1] + [Measure2] the two measures are calculated on their own, then added together. This can potentially be a problem if two measures have overlapping targets, because you could be adding or counting the same thing twice, which would result in an incorrectly inflated total. This is particularly common when both Measure1 and Measure2 use DISTINCTCOUNT().

Imagine this scenario: you're building a report for a temporary staffing company. You have a table of jobs with start dates and end dates. Then you have a separate table of job applications with start dates and end dates. The table ActiveJobs represents jobs currently in-progress and old jobs already ended. JobApplications represents potential future jobs. You want a measure that will give you a weekly count of working temps. It should show both the currently in-progress jobs and the confirmed future jobs. Jobs can start or end at any time during the week. It seems pretty straightforward; you need one measure for current jobs and another for future jobs, then just add them together, right? So the solution should be:

Currently Working = CALCULATE(
	DISTINCTCOUNT(TempTable[TempID]),
	FILTER(
		ActiveJobs,
		ActiveJobs[Start Date] <= LASTDATE(DateTable[Date]) &&
		ActiveJobs[End Date] >= FIRSTDATE(DateTable[Date])
	)
)

Future Working = CALCULATE(
	DISTINCTCOUNT(TempTable[TempID]),
	FILTER(
		JobApplications,
		JobApplications[Status] = "Confirmed" &&
		JobApplications[Start Date] <= LASTDATE(DateTable[Date]) &&
		JobApplications[End Date] >= FIRSTDATE(DateTable[Date])
	)
)

Working Temps = [Currently Working] + [Future Working]

But it turns out that's wrong. Remember that I said:

1. a weekly count of working temps. Not jobs, temps.
2. jobs can start or end at any time during the week.

So what happens if one temp works for you more than once? What if his first job ends on a Tuesday and the next job starts on Wednesday? He would have been counted in both Currently Working and Future Working, so now he counts as 2 people that week when you add them together. This is where the limitation comes in. There's no way that I know of to reference those two measures and tell it to combine them using a distinct count over the total. You can't just say Measure3 = DISTINCT([Measure1] + [Measure2]).* Working Temps would have to include some extra filter criteria so that it only counts Future Working for temps that aren't already counted in Currently Working. It would depend on what other data exists in the various tables involved but for the sake of illustration maybe the solution would look something like:

Working Temps = [Currently Working] + 
CALCULATE(
	[Future Working],
	FILTER(
		TempTable,
		TempTable[Status] <> "On Assignment"
	)
)

The specific solution isn't important. The important thing is that you would need to find some way of explicitly removing the overlap between the two measures.

To illustrate it another way, let's use a Venn Diagram. Measure1 is a distinct count of people for condition x and Measure2 is a distinctcount of people for condition y. Measure3 should be those two combined, but you don't want to double-count anybody. Measure1 counts John, Missy, Ralph, Amy and Larry. Measure2 counts Adam, Nancy, Francis, Missy, Larry, and Emily. So the result you want looks like:

..which would give you a total of 9. But if you just do Measure3 = Measure1 + Measure2 you would get a total of 11, because Measure1 + Measure2 really looks like:

I don't know if that all counts as a limitation, but it is a potential pitfall of simply adding one measure to another. Something to watch out for.

 *yes I know that's not even how DISTINCT() would work even if you could do that. It's just a silly illustration of a point.