Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Creating Margin for mulitple customers

Hi there, I have created a logic for one of the customer “A” with the below formula and it is working fine. PickUpCost-Cost =     Var Cost = IF(Shipments[BilledOn]< DATE(2019,04,01), (1-0.228),  ...
Need Help
bheepatel's avatar
bheepatel
Icon for Resolver IV rankResolver IV
6 years ago

Hi Anonymous 

 

One possible solution is to pivot your table so that you have your table in the same format as Table B below. You can use the pivot options available to you in Power Query.

 

 

Once you have that, you can create the measure similar to the one below, where you can change the Customer and BilledOnDate variables to suit your needs. Below screenshots show you examples for two different customers and billing dates.

 

 

Hope this helps!

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Thanks for the reply.

    I have created “GetValue” measure and I got Values for A,B,C,D

    Below is the formula for Customer ‘A’ and I got the result.

    PC-Cost =
       Var Cost = IF( Shipments[BilledOn]< DATE(2019,04,01), (1-0.228), IF(Shipments[BilledOn]> DATE(2019,04,01)       

                           ||Shipments[BilledOn]< DATE(2020,04,01), (1-0.221), (0.219)) )
       Return
    IF
    ( LOOKUPVALUE('Financials'[Income],'Financials'[Invoice],Shipments[Invoice])-LOOKUPVALUE('Financials'[Cost],'Financials'[Invoice],Shipments[Invoice]) = LOOKUPVALUE('Financials'[Profit],'Financials'[Invoice],Shipments[Invoice]), IF(OR(LOOKUPVALUE('Financials'[ss],'Financials'[Invoice],Shipments[Invoice])<=0.2, LOOKUPVALUE('Financials'[ss],'Financials'[Invoice],Shipments[Invoice])>=0.3),
    Cost*LOOKUPVALUE('Financials'[PC- Income],'Financials'[Invoice],Shipments[Invoice]),
    LOOKUPVALUE('Financials'[PC- cost], 'Financials'[Invoice],Shipments[Invoice])),
    Cost*LOOKUPVALUE('Financials'[PC- Income],'Financials'[Invoice],Shipments[Invoice]))

     

    Now I have to create for all customers (ABCD)
    Could you please let me know how can I use ‘’GetValue” in the above formula