Forum Discussion

jrscaletta's avatar
jrscaletta
Helper II
6 years ago

Weighted column

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.

Need Help

2 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    6 years ago

    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's avatar
      jrscaletta
      Helper II
      6 years ago

      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?