Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Scaling invoice amounts to works

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 IDWork IDAmount
1166.87
2120.93
3197.55
4191.03
5139.62
6296.54
7266.61
8274.83
9313.25
10341.82
11332.55
12446.69
13490.05
14532.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 IDNumber of systemsSystemtype ASystemtype BSystemtype C
13123
2212 
4223 
511  
612  
83123
9212 
1011  
1112  
1412  

 

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:

 

WorkST 1ST 2ST 3Total - mapped invoices
172.3778.2767.81218.46
224.94121.4824.94171.37
348.4439.17-87.61
4----
5-32.25-32.25
Total145.76271.1892.75509.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:

 

WorkST 1ST 2ST 3Total - invoices
1104.69113.2398.09316.01
234.64168.7034.64237.98
348.4439.17-87.61
4---136.74
5-32.25-32.25
Total187.77353.35132.73810.59

 

How can I achieve this? 

Many thanks in advance!

2 ACCEPTED SOLUTIONS
lbendlin
Super User
Super User

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.

View solution in original post

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.

View solution in original post

4 REPLIES 4
Anonymous
Not applicable

It works, thank you very much!

lbendlin
Super User
Super User

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.

Anonymous
Not applicable

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:

https://docs.google.com/spreadsheets/d/1NrjcGOhQ0Z0s4lciFgyj8HOsuSmneemT/edit?usp=sharing&ouid=10626...

 

Also, find below the logic for Work ID 2 and Work ID 4 as you asked. 

 

Work ID 2:

  • ST 1 has an amount of 24.94; ST 2 has an amount of 121.48; ST 3 has an amount of 24.94
  • The total of these three ST's is 171.37, i.e. the amount where there is relationship between a System type and the invoices
  • The total amount of invoices for Work ID 2 is equal to 237.98. The difference between 237.98 and 171.37 is equal to the amount of Invoice ID 7, which has no relationship to any System type

I would then like to have the following output: 

  • Assign 24.94 / 171.36 * 237.98 = 34.64 to ST 1
  • Assign 121.48 / 171.36 * 237.98 = 168.70 to ST 2
  • Assign 24.94 / 171.36 * 237.98 = 34.64 to ST 3
  • Such that the total of Work ID 2 is equal to 237.98

Work ID 4:

  • No relationship to any system type is present, so ST 1, 2 and 3 should remain zero

 

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.