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