Forum Discussion
Subtracting calculated columns from different data sets
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.
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.
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
9 Replies
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 .
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.
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 ?
