Hi,
I'm new to PowerBI, any help would be greatly appreciated! For ease, I have attached a dummy .pbix file with dummy data. You can download it via the link below:
https://drive.google.com/file/d/1RJ_wS1KiabRhuZFlwj4WzXz4ZVNFdwCz/view?usp=sharing
Introduction:
There are four tables in my data, with the following notes:
- Work: A work may consist of multiple invoices
- Invoices: Works have relationships with invoices based on a Work ID. Each invoice has an amount. I have added a calculated column (Revenue_systemtype), which I currently use for my desired output
- Service_reports: (Some of the) invoices have relationships with Service reports, based on an Invoice ID. Service reports categorize the invoices to System types
- Service_reports_unpivot: I have unpivoted the Service_reports table to get my desired result (if there is a more elegant solution, please feel free to correct me on this)
Goals:
I want to segment my Invoices and Works to different System types.
- 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
- The calculated column in my Invoices table gives me the ability to do this
- On the Report page, I have segmented each Work to the three system types (left matrix)
- Also, the total amount of the invoices has been segmented (right matrix)
Questions:
Question 1:
- The totals of these matrices do not add up to the sum of the individual rows. For example, for the right matrix, 145.76 + 271.18 + 92.75 does not equal 352.61. How can I fix this?
Question 2:
- 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
- For example, currently for Work ID = 1, the ratio between the System types is 1 : 2 : 3 = 72.37 : 78.27 : 67.81, and the total Invoice amount for that work is 218.46 when Invoice ID = 3 is not included
- My desired output for Work ID = 1 is the following: a total Amount of Invoice ID 1 + 2 + 3 + 4 + 5 (i.e. 66.87 + 20.93 + 97.56 +91.03 + 39.62 = 316.01), with the ratio between System types 1 : 2 : 3 of abovementioned, resulting in 104.69 : 113.23 : 98.09
- I would like to achieve the same for the total segmentation (i.e. the right matrix in the report)
- How can I achieve this?
I have spent a lot of time on trying to get this done, so I would greatly appreciated any help!
Thanks in advance!
