One Billing, Two Departments, Two Revenues and Two Exctraction Rates

[EMTHB]

Hi All, 

I'm having an issue where I have one billing (let's say 100k for bespoke sparkly suits) but two departments that use it as a revenue source (let's say bespoke tailoring and bedazzling) that have revenue associated with this activity. 

Is there a way that I can have this billing appear in totals for both departments to without having it feed into my business total more than once? So in attached screenshot I'd be looking for the billing to be able to appear as part of both bespoke tailoring and bedazzling  in the right hand table without throwing out the actual business totals in the left hand column. The volume of data I am working with is absolutely huge and requires being put through a workflow management program to get to a useable point so I need a solution that doesn't require any manual editing of the input data ideally. 

Thanks so much in advance

 

[Arpitb12]

Hello,

Probably 
To achieve this in a data model without manual editing of the input data, you can create a new table that allow you to assign multiple departments to each billing entry. This way, a billing entry can contribute to the total of multiple departments without duplicating the overall business total.


Create a table that maps each billing entry to one or more departments

BillingID | Department

1 | Bespoke Tailoring

1 | Bedazzling

2 | Bespoke Tailoring

3 | Bedazzling
You can create this table either manually or by using a transformation process in your data workflow.

In your main data table that contains billing information, create a relationship between the "BillingID" column in the main table and the "BillingID" column in the department mapping table.

Update your measures to consider the relationship with the department mapping table. For example, if you had a measure for total revenue, you might update it to consider the selected departments: