hello,
I have som issues with constructing a measure that does everything I want it to do.
Essentially, I want a measure that computes the share of units sold per customer and product. This share will later be used to allocate indirect costs from defective production per salesorder.
The data (I only specified the relevant data, there is a lot more):
table1: "salesdata" with the columns: Invoice_number,Customer,Product,Units_sold
Table2: "defective_production_cost" with the columns: production_ID, Products, defective_costs
Table3: Consists only of all unique customers. Table 1 and 2 are connected to table 3 with a one to many relationship. ( both directions in the case of table1 )
Importan features:
- When, e.g., 2 customers have bought the same product, and only one of them is selected, the share should stay the same as when both where selected.
- The total share (equivalent to the column total in a table), should allocate all the defective costs, so that it can be used to make a card or similar.
This measure will give me the correct share per sale across all filters, but the total equals the total share of all sales (everything);
DIVIDE (
SUM ( salesdata[units_sold] ),
CALCULATE ( SUM ( salesdata[units_sold] ), ALLEXCEPT(salesdata, salesdata[product]) )
)
Unfortuneately, it would be very hard to provide every aspect of this task here, as the system as a whole is fairly complex. Nevertheless, I am very grateful to those who may give a solution, or just some hits I can work on! Cheers.
