Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
Hi,
I've been stuck at this for hours (frustratingly), any help would be very greatly appreciated!
If you prefer, you could download my data here: https://drive.google.com/file/d/1vn5wWzwWi6EdwiME3E21WBhq6Qgugem1/view?usp=sharing
Data:
There are three tables in my data, with the following notes:
Work: A work may consist of multiple invoices:
Work ID |
1 |
2 |
3 |
4 |
5 |
Invoices: Works have relationships with invoices based on a Work ID. Each invoice has an amount
Invoice ID | Work ID | Amount |
1 | 1 | 66.87 |
2 | 1 | 20.93 |
3 | 1 | 97.55 |
4 | 1 | 91.03 |
5 | 1 | 39.62 |
6 | 2 | 96.54 |
7 | 2 | 66.61 |
8 | 2 | 74.83 |
9 | 3 | 13.25 |
10 | 3 | 41.82 |
11 | 3 | 32.55 |
12 | 4 | 46.69 |
13 | 4 | 90.05 |
14 | 5 | 32.25 |
Service_reports: (Some of the) invoices have relationships with Service reports, based on an Invoice ID. Service reports categorize the invoices to one, two or three different System Types ("ST")
Invoice ID | Number of systems | Systemtype A | Systemtype B | Systemtype C |
1 | 3 | 1 | 2 | 3 |
2 | 2 | 1 | 2 | |
4 | 2 | 2 | 3 | |
5 | 1 | 1 | ||
6 | 1 | 2 | ||
8 | 3 | 1 | 2 | 3 |
9 | 2 | 1 | 2 | |
10 | 1 | 1 | ||
11 | 1 | 2 | ||
14 | 1 | 2 |
Desired output:
I want to segment my Invoices and Works to the three different System types 1, 2 and 3.
When an Invoice has three System types (e.g. Invoice ID = 1 has system type 1, 2 and 3), I want to assign 1/3 of the invoice amount to each system type. When an Invoice has two systems types (e.g. Invoice ID = 2), I want to assign 1/2 of the invoice to each. And when an invoice has 1 system type (e.g. Invoice ID = 5), I want to 100% to that system type. Subsequently, for each work I want to add all invoices together segmented to the different system types:
Work | ST 1 | ST 2 | ST 3 | Total - mapped invoices |
1 | 72.37 | 78.27 | 67.81 | 218.46 |
2 | 24.94 | 121.48 | 24.94 | 171.37 |
3 | 48.44 | 39.17 | - | 87.61 |
4 | - | - | - | - |
5 | - | 32.25 | - | 32.25 |
Total | 145.76 | 271.18 | 92.75 | 509.69 |
For some Works (e.g. Work ID = 1 and Work ID = 2), some invoices have no relationship to a Service report and therefore, the Invoice has not been given a System type. When this is the case, I do want to include all the Amounts of the Invoices in a Work, and use the ratio between the three system types of the Invoices that do have a relationship to a Service Report:
Work | ST 1 | ST 2 | ST 3 | Total - invoices |
1 | 104.69 | 113.23 | 98.09 | 316.01 |
2 | 34.64 | 168.70 | 34.64 | 237.98 |
3 | 48.44 | 39.17 | - | 87.61 |
4 | - | - | - | 136.74 |
5 | - | 32.25 | - | 32.25 |
Total | 187.77 | 353.35 | 132.73 | 810.59 |
How can I achieve this?
Many thanks in advance!
Solved! Go to Solution.
You lost me at
For some Works (e.g. Work ID = 1 and Work ID = 2), some invoices have no relationship to a Service report and therefore, the Invoice has not been given a System type. When this is the case, I do want to include all the Amounts of the Invoices in a Work, and use the ratio between the three system types of the Invoices that do have a relationship to a Service Report
Why wouldn't you distribute it evenly in such a scenario?
Can you walk me through that logic for Work ID 2 and Work ID 4?
Here is the pbix until that step.
I applied the modifications. I am not convinced that this is a viable long term solution though. Better check it thoroughly, and evaluate the performace impact on your actual data set.
Note: This is all implemented as calculated columns. Be careful with the aggregations when you modify the visual.
It works, thank you very much!
You lost me at
For some Works (e.g. Work ID = 1 and Work ID = 2), some invoices have no relationship to a Service report and therefore, the Invoice has not been given a System type. When this is the case, I do want to include all the Amounts of the Invoices in a Work, and use the ratio between the three system types of the Invoices that do have a relationship to a Service Report
Why wouldn't you distribute it evenly in such a scenario?
Can you walk me through that logic for Work ID 2 and Work ID 4?
Here is the pbix until that step.
Hi @lbendlin , thank you very much for taking the time to help me, it is greatly appreciated!
Distributing the amount of the invoices to the already assigned System types is actually exactly what I'm looking for!
For ease, I have transferred the output you already created to excel and created my desired output. You can download it here:
Also, find below the logic for Work ID 2 and Work ID 4 as you asked.
Work ID 2:
I would then like to have the following output:
Work ID 4:
I hope everything is clear, thanks again!
I applied the modifications. I am not convinced that this is a viable long term solution though. Better check it thoroughly, and evaluate the performace impact on your actual data set.
Note: This is all implemented as calculated columns. Be careful with the aggregations when you modify the visual.
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
113 | |
89 | |
84 | |
76 | |
64 |
User | Count |
---|---|
136 | |
111 | |
98 | |
97 | |
92 |