cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
KimRexh
Regular Visitor

Compare two columns in different tables

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! 

 

KimRexh_0-1672306657132.png

 

 

6 REPLIES 6
Ashish_Mathur
Super User
Super User

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
v-yangliu-msft
Community Support
Community Support

Hi  @KimRexh ,

 

Here are the steps you can follow:

1. Create calculated table.

Table1 =
SUMMARIZE(
    'Actual',
    'Actual'[Manager],'Actual'[Role],'Actual'[sort])

vyangliumsft_0-1672369451879.png

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:

vyangliumsft_1-1672369451881.png

3. Create slicers with column headers.

Right-click Copy Actual Table in Power Query to form a new Copy:

vyangliumsft_2-1672369451881.png

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

vyangliumsft_3-1672369451886.png

Click [Vlaue] – Remove Columns.

vyangliumsft_4-1672369451888.png

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

vyangliumsft_5-1672369451888.png

 

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

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; 

KimRexh_0-1672642880317.png

 

archuleta28
Resolver I
Resolver I

Hi @KimRexh ,

 

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

 

archuleta28_5-1672310412303.png

 

 

After clicking OK, Expand ->

archuleta28_6-1672310455627.png

 

 

 

Select columns -> 

archuleta28_7-1672310504100.png

archuleta28_9-1672310534732.png

 

Now, add a conditional column ->

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

 

Result:

archuleta28_10-1672310694024.png

 

 

 

 

 

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? 

 

@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



!! Microsoft Fabric !!
Microsoft Power BI Learning Resources, 2023 !!
Learn Power BI - Full Course with Dec-2022, with Window, Index, Offset, 100+ Topics !!
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Proud to be a Super User! !!

Helpful resources

Announcements
May 2023 update

Power BI May 2023 Update

Find out more about the May 2023 update.

Submit your Data Story

Data Stories Gallery

Share your Data Story with the Community in the Data Stories Gallery.

Top Solution Authors