cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## 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.

2 ACCEPTED SOLUTIONS
Frequent Visitor

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.

Microsoft Employee

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

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
9 REPLIES 9
Resident Rockstar

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.

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 .

Frequent Visitor

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.

Resident Rockstar

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 ?

Frequent Visitor

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.

Resident Rockstar

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

Frequent Visitor

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.

Resident Rockstar

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

Frequent Visitor

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.

Microsoft Employee

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

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors