cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
CapnShanty Frequent Visitor
Frequent Visitor

Need to get difference between all values of column1 and column2?

So I have two tables I've brought in from a database, each with the columns MetricName and MetricValue (they are for different months).

 

I need to have a table showing the % change between every single MetricName. I have tried many of the solutions on this forum for a problem similar to this, but they do not work as they all tell me "a single value cannot be determined" etc. 

 

I tried merging them in a query and then doing it, and I still get the same error. This does not change if I do a new measure, a new column, or a new quick measure; when it works, I get nothing but a single blank value. This can't be that hard...

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
drewlewis15 Member
Member

Re: Need to get difference between all values of column1 and column2?

Assuming I am understanding the question correctly....

 

Join your two tables based on MetricName

 

Then create the following measure (replacing all references of tables and columns with your actual table and column names):

 

Difference =
VAR SumOfTable1 = SUM(Table1[MetricValue])
VAR SumOfTable2 = SUM(Table2[MetricValue])
RETURN SumOfTable1 - SumOfTable2

 

Below is the test data I setup to show this measure:

 

Table1:

Table1.png

 

Table2:

Table2.png

 

Join:

Join.png

 

Measure:
Measure.png

 

Output:
Output.png

 

 

1 REPLY 1
Highlighted
drewlewis15 Member
Member

Re: Need to get difference between all values of column1 and column2?

Assuming I am understanding the question correctly....

 

Join your two tables based on MetricName

 

Then create the following measure (replacing all references of tables and columns with your actual table and column names):

 

Difference =
VAR SumOfTable1 = SUM(Table1[MetricValue])
VAR SumOfTable2 = SUM(Table2[MetricValue])
RETURN SumOfTable1 - SumOfTable2

 

Below is the test data I setup to show this measure:

 

Table1:

Table1.png

 

Table2:

Table2.png

 

Join:

Join.png

 

Measure:
Measure.png

 

Output:
Output.png