Forum Discussion

jrscaletta's avatar
jrscaletta
Icon for Helper II rankHelper 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.

2 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion

    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
      Icon for Helper II rankHelper II

      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?