Compare two columns in different tables

[KimRexh]

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! 

[Ashish_Mathur]

Hi,

Add a column in each Table with the title of Type.  The entries in this column should be Actual and Budget.  Append the 2 tables.  Select all columns other than the Year columns, right click and select "Unpivot Other Columns".  Create a matrix visual - add manager and Role to rows, Type and Attribute to columns.  Write this measure

measure = sum(Data[Value])

Hope this helps.

[v-yangliu-msft]

Hi  @KimRexh ,

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]<>[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.

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

[KimRexh]

Thank you for your solution. So doing it this way I set the 1944 column static right?
What I would like, and I could have made it more clear in the desc. is I would like to compare all values (1944 (YYWW) goes from 2019-today) with two slicers.

Like this; 

[archuleta28]

Hi @KimRexh ,

You can use "Merge Queries as new" and then add a conditional column like below for comparision:

After clicking OK, Expand ->

Select columns -> 

Now, add a conditional column ->

Comparision = if(Merge1Forecast] = Merge1[Actual], 0, 1)

Result:

[KimRexh]

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. 

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? 

[amitchandak]

@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-solutio...

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