Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors