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.
Hi Guys, new user here.
What would be the best way to simultaneously separately filter and combine two datasets visually?
I have a table of invoiced amounts that I wish to filter post a specific date and sum by month.
I also have a table of projects with forecast amounts that need to be filtered depending on job status to exclude those with specific statuses (dead, unsuccessful ...) and then summed with the invoiced values by month and displayed graphically.
Is it possible to separate filters and combine within the graphic display options or am I looking at pre-filtering and combining the data into a new table, column, etc.
Thanks in advance for your assistance.
Solved! Go to Solution.
In this scenario, to combine two datasets data together, you should aggregate the rows in your Dataset 1 first. Then build the relationship to Dataset 2. Then you can just create a calculated column combine 'Invoice Amount' and 'Value'.
1. Create a table aggregating the rows in Dataset 1.
Table = SUMMARIZE(Table3,Table3[Client],Table3[Date],"Amount",CALCULATE(SUM(Table3[Invoice Amount])))
2. Build the relationship to Dataset 2.
3. Create a calculated column like below:
Total Value = 'Table'[Amount]+RELATED(Table4[Value])
Regards,
Can you post sample data and expected results?
Dataset 1
Client | Invoice Amount | Date |
X | 200 | Jan |
X | 1000 | Jan |
Y | 400 | May |
Z | 600 | Oct |
Dataset 2
Project | Value | Status | Est. Completion Date |
A | 450 | To Do | Jan |
B | 900 | To Invoice | May |
C | 1000 | Complete | Oct |
What I am trying to do is display the total amount from invoice and forecasted work on a month by month basis. However I to filter Dataset 2 by Status, in this case to remove "Complete" entries. The two datasets share a common identifier code column to link them if needed.
The end result for the above example would be:
Month | Value |
Jan | 1650 |
May | 1300 |
Oct | 600 |
In this scenario, to combine two datasets data together, you should aggregate the rows in your Dataset 1 first. Then build the relationship to Dataset 2. Then you can just create a calculated column combine 'Invoice Amount' and 'Value'.
1. Create a table aggregating the rows in Dataset 1.
Table = SUMMARIZE(Table3,Table3[Client],Table3[Date],"Amount",CALCULATE(SUM(Table3[Invoice Amount])))
2. Build the relationship to Dataset 2.
3. Create a calculated column like below:
Total Value = 'Table'[Amount]+RELATED(Table4[Value])
Regards,
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 |
---|---|
114 | |
100 | |
75 | |
73 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |