Subtracting calculated columns from different data sets

[Stymied9]

I have 5 different data sets uploaded.  In each of those 5 data sets, I have a calculated column (CPK) (example: Column X / Column Y = Z).  I then have those 5 calculated columns entered into a Table Visual.  Now what I'd like to do is create a new column in the visual that whenever there is data in both column FY15 & column FY16 (or any two columns) in the visual, I would like the following to happen FY16 - FY15 = New Column, which would be the difference of the two.  So for the first line, it would be 84.52 - 82.34 = 2.18 or 2.18.

Line two would be 14.96 - 37.55 = -22.59 or (22.59)

I hope that makes sense.

 

[Stymied9 · Accepted solution]

I was able to get it sorted out.  I believe it was the CPK's in the data sets that were changed to an average which was messing everything up.  I needed to nto use those, but create a measure for each data set, then create anew measure FY16 - FY15, which gave me the solution i needed.

[Baskar]

I have few question for u .

1. This is your final table ? or u have 5 tables ?

2. y u created calculated column ?

If u have table pls share that table and tell what the result u expected.

Now coming to your post.

If u want create a Difference between 2016 to 2015, Simple 

Create measure  Measure = Sum(Average of 2016) - Sum(Average of 2015)

this is based on my understand , let me know if my understand is wrong .

[Stymied9]

This will be my final and only table. 

All of my data sets have repetetive information in them which I'm using as my first column in the table.  I created a primary key in each of the 5 datasets (Source Company Name, Country - Destination Company Name, Country), which will have a CPK in the corresponding FY columns in the table if there was a shipment.  But as I said, there will be multiple entries of most of them, and I could not connect the sheets together because it was a many to many relationship.  So I created a separate data set with the full list of primary key information and removed the duplicates.  And used that as the connector to all of the sheets.  And that is being used as the first column in my table. 
So for each row in my data sets, I made the calculated column Cost / Weight = CPK.  When I put that in the table, it would not allow me to enter more than one FY, because it would start pulling in the individual lines rather than working like a pivot table and averaging them out under one primary key line.  So I changed them to an AVG CPK in the table and it allowed me to enter all 5 FY's under an individual PK line.

Hope that explains it a bit better.

[Baskar]

Cool my dear friend. 

I got the reson behind the Table design. Now do u want subtracting two column which u have in same table am i right ?

[Stymied9]

That I have in the Visual Table, yes.  FY16 - FY15 = The difference.

They are not averaged out in the data sets, they are per row.  So they only get averaged out in the Visual Table, and that is what I want to see the difference of.  And I can't figure out how to do it or know if it is even possible.

[Baskar]

i don't understand where u struggeling .

Create new measure like 

Measure = Sum(Value 2016) - Sum(Value2015)

and use it in visual,

If am not correct , let me know 

[Stymied9]

I am getting the following error when utilizing that formula:

The SUM function only accepts a column reference as an argument.

I also tried SUMX(Values(FY16CPK)-SUMX(Values(FY15 CPK), but i get the error:

Too few arguments were passed to the SUMX function. The minimum argument count for the function is 2.

[Baskar]

Cool my dear friend.

Try this. Create new measure or column whatever u want

Measure  = CALCULATE(SUMX(Table Name ,SUM(Value2016) - SUM(Value2015)))

Let me know if it is not helping u

[Stymied9 · Accepted solution]

I was able to get it sorted out.  I believe it was the CPK's in the data sets that were changed to an average which was messing everything up.  I needed to nto use those, but create a measure for each data set, then create anew measure FY16 - FY15, which gave me the solution i needed.

[v-yuezhe-msft · Accepted solution]

Hi @Stymied9,

Glad to hear the issue is solved, you can accept helpful replies as solution, that way, other community members will easily find the solution when they get same issue.

Thanks,
Lydia Zhang