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!
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.
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])),)
Measure2 = SUMX( FILTER(ALL('Forecast'), 'Forecast'[sort]=MAX('Table1'[sort])),)
Flag = IF( [Measure1]<>[Measure2],1,0)
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.
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.
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)
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
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
Find out more about the May 2023 update.
Share your Data Story with the Community in the Data Stories Gallery.