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

Error while comparing data from 2 tables

Hello Everyone, need your help....

 

I get inaccurate data when I try to compare two years worth Budget Data across different 'Areas' ('Areas' = APAC, EMEA, Americas). The sources of data are 2 different Sharepoint Lists, the visualization's output values differ depending on which Table i choose the "Axis" field from (see screenshot below)

 

Source of Data:

2 Sharepoint Lists - Every year's data is stored in its own list. Therefore, 2017 budget data is stored in "2017 Budget Sharepoint List" & 2018 budget data in "2018 Budget Sharepoint List".

 

  • I imported both lists into PowerBI desktop and they show up as 2 tables
  • I created the relationship between the 2 tables using 3rd Table. This 3rd table is list of Offices in every Country (this list of offices is also a Sharepoint List called ListofOffices. Individual Offices is unique field in both the Budget Sharepoint Lists

I think the problem is because of some relationship issue, but i don't know how to fix it. Can you offer any advice?

 

ChartWhenFY17AreaIsOnTop.PNGFY17 data is correct but FY18 is incorrect when the 'Axis' field is 'Area' column from FY17 table        ClusteredColumnSettingsAreaFromFY17.png'Area' field is from FY17 table ChartWhenFY18AreaIsOnTop.PNGFY18 data is correct but FY17 is incorrect when the ''Axis' field is 'Area' column from FY18 table          ClusteredColumnSettingsAreaFromFY18.png'Area' field is from FY18 table

Relationship.pngRelationship is based on unique field call "Sites" (aka "Offices")

 

 

Data Structure

Both Budget Sharepoint Lists have identical columns. For example, "Area" column has list of areas (EMEA, APAC, AMERICAS), "Budget Data" column has the actual numeric $ value.

 

Appreciate any help

1 ACCEPTED SOLUTION

Accepted Solutions
aknair Frequent Visitor
Frequent Visitor

Re: Error while comparing data from 2 tables

Hello @GilbertQ & @v-chuncz-msft,

 

Apologize for the delayed response, I was trying out your suggestions -

@GilbertQ your suggestion to append the two tables and then compare the data worked.

 

I tried the other suggestions but they didn't work -

1. Change the cross filter direction on the relationships - FY17 Budget Data and FY18 Budget Data from BOTH to SINGLE?

2. Try to drag "Area" field from table Sites.

p.s. i also noticed the "Site" column of FY17 table had few extra entries that wasn't in "Site" column of FY18 table; corrceted this but the issue still persisted.

 

I used the "Combine" options from the PowerBI Desktop App (Query Editor) to append the two tables.

Once i combined the 2 tables and created 1 table there was no relationship to manage so everything became easy.

 

Thank you both for your suggestions.

/rgds

3 REPLIES 3
Super User
Super User

Re: Error while comparing data from 2 tables

Hi @aknair


Can you change your cross filter direction on your relationships to the FY17 Budget Data and FY18 Budget Data from BOTH to SINGLE?

 

Also you could possibly Append the Tables together, and then create One measure and use the Year column (If you have it to slice the data?

Did I answer your question? Mark my post as a solution!
"Proud to be a Datanaut!"
Community Support Team
Community Support Team

Re: Error while comparing data from 2 tables

@aknair,

 

Also try to drag "Area" field from table Sites.

https://powerbi.microsoft.com/en-us/documentation/powerbi-desktop-create-and-manage-relationships/

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
aknair Frequent Visitor
Frequent Visitor

Re: Error while comparing data from 2 tables

Hello @GilbertQ & @v-chuncz-msft,

 

Apologize for the delayed response, I was trying out your suggestions -

@GilbertQ your suggestion to append the two tables and then compare the data worked.

 

I tried the other suggestions but they didn't work -

1. Change the cross filter direction on the relationships - FY17 Budget Data and FY18 Budget Data from BOTH to SINGLE?

2. Try to drag "Area" field from table Sites.

p.s. i also noticed the "Site" column of FY17 table had few extra entries that wasn't in "Site" column of FY18 table; corrceted this but the issue still persisted.

 

I used the "Combine" options from the PowerBI Desktop App (Query Editor) to append the two tables.

Once i combined the 2 tables and created 1 table there was no relationship to manage so everything became easy.

 

Thank you both for your suggestions.

/rgds