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

Works and invoices

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!

 

4 REPLIES 4
amitchandak
Super User
Super User

@Anonymous , Based on what I got, measure like

 

Sumx(Summarize(Table, Table[Invoice ID]. Table[Work ID]), [Measure])

 

or

 

Sumx(Summarize(Table, Table[Invoice ID]. Table[Work ID],"_1", [Measure]),[_1])

Anonymous
Not applicable

Hi @amitchandak, thanks for your quick response. I don't understand it though. Could you be so kind to be a little more specific? In which table should I put the measure? To which tables and measures are you referring? [edit] and which of the two questions are you addressing? 

 

@Anonymous , Why this formula as not considered

Revenue_systemtype m = DIVIDE(sum(Invoices[Amount]), sum(Service_reports[Number of systems]))
Anonymous
Not applicable

Hi @amitchandak, although I appreciate your effort, the suggested measure is not achieving the desired result. Am I missing something here? 
Also, are you trying to answer question 1 or question 2?

 

 

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.