Forum Discussion

T-Joyce's avatar
T-Joyce
Frequent Visitor
6 years ago

Sales Data and Price Lists

Hi,

I have two main tables of data (Sales and Net Price) plus a date table which includes season year (eg. Spring 19), product table and customer table. The sales table lists all sales transactions by quantity, by product, by date, by customer etc. and the net price table lists all net prices by customer, by product, by season. 

Example of data in sales table (Date = 05/05/2019, Product = 123, Customer = 12345, Quantity = 100). 

Example of data in net price table (Season = Spring 19, Product = 123, Customer = 12345, Net Price = 10.00)

I am hoping there is a simple measure I can create to work out Net Value?

Need Help

5 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

    Create a new column in sales

    price in sales = 
maxx(filter(price, sales[Product]=price[product] && sales[customer] =price[customer] && format(sales[date],"YY")= right(Season ,2)),price[net price])

     

    Modify date logic as per need.

     

    Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
    Thanks.

    My Recent Blog - https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601

  • T-Joyce's avatar
    T-Joyce
    Frequent Visitor
    6 years ago

    Sorry if I was not clear. I have included a sample line from my relevant data tables below, hope this helps to explain it better.

     

    Sales Table:

    Transaction DateCustomer CodeItem CodeQuantity (Packs)Quantity (Kg/Lt)
    10 April 20171234512360600

     

    Net Price Table:

    SeasonYearCustomer CodeItem CodeNet Price (Kg/Lt)
    Spring 17123451236.00

     

    Date Table:

    DateYYMonthNumSeason

    SeasonYear

    10 April 2017174Spring

    Spring 17

     

    I have relationships set up for Customer Code to a customer table, Item code to an item table and Transaction date to my date table.

    For reference we we have two seasons we work to: Jan-Jun = Spring, Jul-Dec = Autumn. Also the Net Price varies depending on SeasonYear, Customer and Item. 

     

    What I am looking to end up with is either a new column or a measure in my sales table which multiplies Sales[Quantity (Kg/Lt)] by Net Price[Net Price (Kg/Lt)] when the SeasonYear, Customer Code and Item Code match. So for this example the [Net Sales] would be 3,600.00.

     

    My net price table takes the data from an excel sheet that is manually updated each season so if I need to add any extra columns to that to help with this then I can do that.

     

    Thanks in advance for any help.

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      HI T-Joyce ,

      You can try to use the following measure formula if it meets for your requirement:

      Measure =
VAR currCode =
    SELECTEDVALUE ( Sales[Item Code] )
VAR currDate =
    MAX ( Sales[Transaction Date] )
VAR seasonYear =
    CALCULATE (
        MAX ( 'Calendar'[SeasonYear] ),
        FILTER ( ALLSELECTED ( 'Calendar' ), [Date] = currDate )
    )
VAR _price =
    CALCULATE (
        MAX ( Price[Net Price (Kg/Lt)] ),
        FILTER (
            ALLSELECTED ( Price ),
            Price[Item Code] = currCode
                && Price[SeasonYear] = seasonYear
        )
    )
RETURN
    CALCULATE (
        MAX ( Sales[Quantity (Kg/Lt)] ),
        VALUES ( Sales[Item Code] ),
        VALUES ( Sales[Transaction Date] )
    ) * _price

      Regards,
      Xiaoxin Sheng

      • T-Joyce's avatar
        T-Joyce
        Frequent Visitor
        6 years ago

        Hi, thank you for your response, this has almost solved it however, the 'customer code' needs to be in here somewhere as we have different net prices for each customer in each season.