Error while comparing data from 2 tables

[aknair]

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?

FY17 data is correct but FY18 is incorrect when the 'Axis' field is 'Area' column from FY17 table        'Area' field is from FY17 table FY18 data is correct but FY17 is incorrect when the ''Axis' field is 'Area' column from FY18 table          'Area' field is from FY18 table

Relationship 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

[aknair · Accepted solution]

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

[v-chuncz-msft]

@aknair,

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

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

[GilbertQ]

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?