Find differences between 2 selected rown in a table

[johnstrang]

Hello everyone,

In my work we have monthly mailing campaigns to selected donors from our database, where each campaign record has a number of fields, for example

• Number of people mailed
• Number of reponses with donation
• Value of donations received

What I want to do is simply select 2 campaigns by their reference number (via a filter) and calculate the differences in these values.

A very simple example:

Campaign	Nr Mailed	Nr Responses	Value of Donations
11111	1000	50	2000
22222	1100	40	1900
Differences	-100	10	100

Displaying the values for each campaign is very easy, but finding the differences is not ...

Any suggestions gratefully received!

Thank you in advance and kind regards,

John

[mark_endicott · Accepted solution]

@johnstrang - This will take multiple steps.

1) Set up two DAX Calculated tables that will allow you to select a Campaign:

Campaign 1 = VALUES( 'Table'[Campaign] )

Campaign 2 = VALUES( 'Table'[Campaign] )

2) If you get a relationship created with either of these tables, ensure they are deleted. 

3) Create a measure that finds the values you want based on a slicer selection and then performs the calculation:

Difference = 

VAR _campaign_1 = SELECTEDVALUE( 'Campaign 1'[Campaign] )

VAR _campaign_2 = SELECTEDVALUE( 'Campaign 2'[Campaign] )

VAR _value_campaign_1 = CALCULATE( SUM( 'Table'[Column] ), KEEPFILTERS( 'Table'[Campaign] = _campaign_1 ))

VAR _value_campaign_2 = CALCULATE( SUM( 'Table'[Column] ), KEEPFILTERS( 'Table'[Campaign] = _campaign_2 ))

RETURN
_value_campaign_2 - _value_campaign_1

Just replace my 'Table' and [Column] references with the names from your model. 

If I answered your question please mark my post as the solution, it helps others with the same challenge find the answer!

[v-tejrama · Accepted solution]

Hi @johnstrang ,

After thoroughly reviewing the details you provided, I was able to reproduce the scenario, and it worked on my end. I have used the sample data on my end and successfully implemented it.  

I am also including .pbix file for your better understanding, please have a look into it:

Hope this will help you resolve your issue, if you need any further assistance, feel free to reach out.

If this post helps, then please give us ‘Kudos’ and consider Accept it as a solution to help the other members find it more quickly.

Thank you for using Microsoft Community Forum.

Tejaswi

[johnstrang]

Thank you so much - this works well. I now need to duplicate the various measures I need to complete my report.

Regards,

John

[mark_endicott · Accepted solution]

@johnstrang - This will take multiple steps.

1) Set up two DAX Calculated tables that will allow you to select a Campaign:

Campaign 1 = VALUES( 'Table'[Campaign] )

Campaign 2 = VALUES( 'Table'[Campaign] )

2) If you get a relationship created with either of these tables, ensure they are deleted. 

3) Create a measure that finds the values you want based on a slicer selection and then performs the calculation:

Difference = 

VAR _campaign_1 = SELECTEDVALUE( 'Campaign 1'[Campaign] )

VAR _campaign_2 = SELECTEDVALUE( 'Campaign 2'[Campaign] )

VAR _value_campaign_1 = CALCULATE( SUM( 'Table'[Column] ), KEEPFILTERS( 'Table'[Campaign] = _campaign_1 ))

VAR _value_campaign_2 = CALCULATE( SUM( 'Table'[Column] ), KEEPFILTERS( 'Table'[Campaign] = _campaign_2 ))

RETURN
_value_campaign_2 - _value_campaign_1

Just replace my 'Table' and [Column] references with the names from your model. 

If I answered your question please mark my post as the solution, it helps others with the same challenge find the answer!

[johnstrang]

Hi Mark

Many thanks for your prompt response - I'll give it a go and let you know how it works.

Regards,

John