Compare two columns in different tables

Question

Hi, I have two disconnected tables (no relationship) that I want to compare.

I want to have a slicer to choose the week of comparison.

Actual table and Forcast table. I want to have a view like on the right of the picture below.

Any help is appreciated!

insightsbyv · Answer

Hi KimRexh&nbsp;,&nbsp;You can use "Merge Queries as new" and then add a conditional column like below for comparision:&nbsp;&nbsp;&nbsp;After clicking OK, Expand -&gt;&nbsp;&nbsp;&nbsp;Select columns -&gt;&nbsp;&nbsp;Now, add a conditional column -&gt;Comparision =&nbsp;if(Merge1Forecast] = Merge1[Actual], 0, 1)&nbsp;Result:&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;

kimrexh · Answer

Thank you for your reply!When I do this I get a new row for each row when I expand the table with columns from table2.&nbsp;&nbsp;Also, I have tried creating field parameters for the columns so that I can create a slicer for the columns but is there a alternative way of creating slicer with column header?&nbsp;&nbsp;

amitchandak · Answer

KimRexh , a better way is to create common dimensions and use those 
https://amitchandak.medium.com/power-bi-when-i-asked-you-to-create-common-tables-a-quick-dax-solution-8e3eccb41bda
Power BI- DAX: When I asked you to create common tables: https://youtu.be/a2CrqCA9geM
Power BI- Power Query: When I asked you to create common tables: https://youtu.be/PqfGW6pl1Sw

anonymous · Answer

Hi&nbsp; KimRexh&nbsp;,
&nbsp;
Here are the steps you can follow：
1. Create calculated table.
Table1 =
SUMMARIZE(
    'Actual',
    'Actual'[Manager],'Actual'[Role],'Actual'[sort])

2. Create measure.
Measure1 =
SUMX(
    FILTER(ALL(Actual),
    'Actual'[sort]=MAX('Table1'[sort])),[1944])Measure2 =
SUMX(
    FILTER(ALL('Forecast'),
    'Forecast'[sort]=MAX('Table1'[sort])),[1944])Flag =
IF(
    [Measure1]&lt;&gt;[Measure2],1,0)
Result:

3. Create slicers with column headers.
Right-click Copy Actual Table in Power Query to form a new Copy:

Select all columns of Copy Table – Transform – Unpivot columns.

Click [Vlaue] – Remove Columns.

Select [Attribute] – Right-click – Remove Duplicates.

&nbsp;
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

ashish_mathur · Answer

Hi,
Add a column in each Table with the title of Type.&nbsp; The entries in this column should be Actual and Budget.&nbsp; Append the 2 tables.&nbsp; Select all columns other than the Year columns, right click and select "Unpivot Other Columns".&nbsp; Create a matrix visual - add manager and Role to rows, Type and Attribute to columns.&nbsp; Write this measure
measure = sum(Data[Value])
Hope this helps.