I need to make a card that display the difference between two other cards

[scoledav]

One card is the sum of a filtered column in a table from an SQL Server connection and the second card is the result of a direct SQL query used when I set up the data source. I didn't want to use a direct SQL query, however there are too many tabales in the database to display the one that I want. I am showing one type of revenue on one card, another type of revenue on another and need to display the difference between the two on a third card. I've tried creating a measure out of the two and subtracting the two measures however I am not able to create a measure out of the second card as it is a query with one row and column, a single cell. I need to either figure out how to set up the data source for the second card correctly so I can access the table I need to and go from there or find a way to subtract the one from the other. What do you suggest?

Thank you!

[v-agajavelly · Accepted solution]

Hi @scoledav ,

If the above response helped solve your issue, please consider clicking "Accept as Solution" and giving it a kudos it helps others in the community who may run into the same challenge.

Thanks again for engaging with the community, and happy reporting in Power BI.

Thanks,
Akhil.

[v-agajavelly]

Hi @scoledav ,

You're most welcome. So glad to hear the first revenue card is working now you're doing great, seriously.

And no worries at all about the second one  this is a very common scenario in Power BI when pulling in a single value from a direct SQL query. It feels like it should be simple and it actually is once you know the trick.

If your SQL query returns just one row and one column, for example. You can create a measure like this.

Revenue_SQL = SELECTEDVALUE(YourQueryTable[RevenueValue])

This tells Power BI to grab that single cell’s value  and now it becomes a proper measure, so you can use it in calculations or visuals like any other.
If you want to be extra safe, just in case the query ever returns more than one row), you can do.

Revenue_SQL = COALESCE(SELECTEDVALUE(YourQueryTable[RevenueValue]), 0)

That way it will default to 0 instead of blank if something goes sideways.

Now your difference measure becomes.
Revenue_Difference = [Revenue_2025] - [Revenue_SQL].

 
Regards,
Akhil.

[scoledav]

Amazing! That did the trick.  Thank you so much. All three cards are working perfect now.

[v-agajavelly]

Hi @scoledav ,

Thanks so much for your input, @amitchandak  really appreciate your time.

@scoledav  great you're absolutely on the right track! The issue you're running into with your current measure.

Measure1 = CALCULATE(SUM('BATCHREVENUE (3)'[AMOUNT]), DATESBETWEEN('BATCHREVENUE (3)'[DATE], DATE(2025,1,1), DATE(2025,12,31)))

This is happening because DATESBETWEEN expects a column with unique date values, typically from a proper Date table. If your 'BATCHREVENUE (3)'[DATE] column contains duplicate dates (which is very common in transaction tables), Power BI throws an error.
As a quick workaround, you can rewrite your measure using FILTER instead of DATESBETWEEN.

Measure1 = CALCULATE(SUM('BATCHREVENUE (3)'[AMOUNT]), FILTER('BATCHREVENUE (3)', 'BATCHREVENUE (3)'[DATE] >= DATE(2025,1,1) &&'BATCHREVENUE (3)'[DATE] <= DATE(2025,12,31)))

This works directly on your data no need for a separate Date table and avoids the error altogether.

Once both revenue values are available as measures (whether from a table or a direct SQL query), you can then simply create another measure for the difference.

RevenueDiff = [Measure1] - [Measure2]

Drop that into your card visual, and you're good to go. Hope this helps in the right direction.

Thanks,
Akhil.

[scoledav]

Thank you! That worked for the first revenue card! I am trying to make the second revenue card into a measure however it is from a direct SQL query and is just one cell. Can I not make this into a measure? Sorry, I know this should be easy. I appreciate you helping me.

[amitchandak]

@scoledav , Seem like you are using a visual level filter. Instead of that, create a measure with those filters, then you can take the difference. Or make both measures driven by slicer, then you can take diff 

[scoledav]

Okay, thank you! I tried making a measure and filtering for date. I am new to DAX so I'm having a little trouble. I know this is easy. Anything stick out as incorrect here?

Measure1 = CALCULATE(sum('BATCHREVENUE (3)'[AMOUNT]),DATESBETWEEN ('BATCHREVENUE (3)'[DATE], DATE(2025,01,01),DATE(2025,12,31)))

When I make a card and use the measure as a source I get the following error: