Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Report 1
Name | ID | Amount | Month | Day |
Jason | 1001 | 100 | May | 23 |
Jason | 1001 | 105 | May | 24 |
Sandy | 1002 | 200 | May | 23 |
Sandy | 1002 | 250 | May | 24 |
Report 2
Name | ID | Amount | Month | Day |
Jason | 1001 | 103 | May | 23 |
Jason | 1001 | 104 | May | 24 |
Sandy | 1002 | 200 | May | 23 |
Sandy | 1002 | 280 | May | 24 |
I'm a newbie to Power BI and did a lot of research, but can't really find the solution that I'm looking for..
On one tab, I have Report 1 and on another tab, I have Report 2. They're two different reports that are I extract using two different softwares. If I want to see the difference between the two how would I do it?
So I want to see the difference for Jason for May 23. I want the result to show 3. (report 2 - report 1) (See WANT table)
And if it's possible, I'd like to create a line graph between the two reports who the difference...
Technically two questions:
1) Formula to insert to create my own measure
2) Line graph to compare the two reports
Appreciate help in advance!!!
WANT
Name | ID | Amount | Month | Day |
Jason | 1001 | 3 | May | 23 |
Jason | 1001 | -1 | May | 24 |
Sandy | 1002 | 0 | May | 23 |
Sandy | 1002 | 30 | May | 24 |
Solved! Go to Solution.
Hi @jeongs1
You could try the following calculated table
New Table = SELECTCOLUMNS( FILTER( CROSSJOIN(Report1,Report2) , 'Report1'[Name] = 'Report2'[Name] && 'Report1'[ID] = 'Report2'[ID] && 'Report1'[Month] = 'Report2'[Month] && 'Report1'[Day] = 'Report2'[Day] ), "Name",'Report1'[Name] , "ID",'Report1'[ID] , "Amount" , 'Report1'[Amount] - 'Report2'[Amount] , "Month" , 'Report1'[Month] , "Day" , 'Report1'[Day] )
which for me returns this :
I'd recommend adding a year column though....
Hi @jeongs1
You could try the following calculated table
New Table = SELECTCOLUMNS( FILTER( CROSSJOIN(Report1,Report2) , 'Report1'[Name] = 'Report2'[Name] && 'Report1'[ID] = 'Report2'[ID] && 'Report1'[Month] = 'Report2'[Month] && 'Report1'[Day] = 'Report2'[Day] ), "Name",'Report1'[Name] , "ID",'Report1'[ID] , "Amount" , 'Report1'[Amount] - 'Report2'[Amount] , "Month" , 'Report1'[Month] , "Day" , 'Report1'[Day] )
which for me returns this :
I'd recommend adding a year column though....
You need to create 2 "lookup" tables. One that has just the unique people, and one that has just the unique dates.
You can probably pull that off in Power Query, but I'm a DAX guy, so... I would create a calculated table:
People = UNION(DISTINCT(Report1[Name]), DISTINCT(Report2[Name]))
You probably will want to create a single column with an *actual* date as well. Say,
DateKey = DATEVALUE(Report1[Month] & " " & Report1[Day] & ", 2017")
Once you do that (to both tables), you can create a calendar table by Create Table again:
Dates = CALENDARAUTO( )
Then you can create relationships between:
- people lookup, and both reports
- calendar table, and both reports (to the new DateKey column)
Then, with your model all nice, you can create some easy measures like:
Total Amount, Report 1 = SUM(Report1[Amount])
Total Amount, Report 2 = SUM(Report2[Amount])
Delta = [Total Amount, Report 1] - [Total Amount, Report 2]
Then you can build whatever tables/charts/visuals you want by using values from the date table + people from the lookup table... against these new measures.
Good luck!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
99 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
110 | |
91 | |
84 | |
66 |