Weighted column

[jrscaletta]

Hi

I'm trying to distribute a cost by quantity. I explain:

I have a grid with sales by customer. In another table, I have fix cost for sales agent between dates. This sales agent is relationated to some customers. OK, the relationship works well.

The table of fix cost:

From date - to date - cost - sales agent
01/01/2020 - 31/01/2020 - 122.000€ - 3838

Then, the grid:

LINK GRID IMAGE 

Then, in the grid, I obtain the date only for the customers who have the sales agent 3838 associated.

€ comercial = 
    CALCULATE(
    SUM('€ Comercial'[Euros]);
DATESBETWEEN(
    CUSTINVOICEJOUR[INVOICEDATE];
    FIRSTDATE('€ Comercial'[Desde Fecha]);
    LASTDATE('€ Comercial'[Hasta Fecha]));
    USERELATIONSHIP(CUSTINVOICEJOUR[SALESGROUP];'€ Comercial'[Comercial]);CROSSFILTER('€ Comercial'[Comercial];CUSTINVOICEJOUR[SALESGROUP];Both))
    

First, It shows all the cost (122.000) for any customer it has Sales Agent 3838 associated. I would to show in column € comercial the weighted cost by column "Cantidad (KG)" ... Then, when more "Cantidad (KG)", more "€ comercial", but the sum of € comercial needs to be 122.000. 

Then, the same for colum "€ comercial/kg":

€ comercial/kg = DIVIDE('€ Comercial'[€ comercial];[KG TOTALES])

KG TOTALES = CALCULATE(SUM(Cantidad (KH));ALLEXCEPT(CUSTINVOICEJOUR;CUSTINVOICEJOUR[SALESGROUP]))

**SALES GROUP is SALES AGENT.

How can I do?

Thanks in advance.

[Greg_Deckler]

Not 100% clear. I think that some additional sample data and the expected output from that sample data would be helpful. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

So, are you trying to distribute the cost across the date range or across the customer base or both? Maybe this? https://community.powerbi.com/t5/Quick-Measures-Gallery/Periodic-Billing/m-p/409365#M148

[jrscaletta]

Hi Greg.

Thanks for your quick response.

The goal is to determine how many cost per customar based on their quantitity (KG).

Then, I need to take all the sales by customer where sales agent = 309904. The total cost is 122.000€

In the grid, the lines it has value in [€ comercial] column, are the customers of sales agent = 309904. 

I need to distribute the total cost 122.000€ by quantity, then:

First row:

- Quantity (KG) - 54.355,97

Second Row:

- Quantity (KG) - 47.268,48

Then:

TOTAL QUANTITY = 54.355,97 + 47.268,48 = 101.624 KG

Then: ( FIRST ROW QUANTITY (54.355,97) / TOTAL QUANTITY (101.624 ) * TOTAL COST (122.000) = 65.254€

How can I obtain this?