Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Data allocation in retailer sales model

Dear PowerBI Community,   I'm trying to build a sales model for a company which sell to supermarkets. The goal is to calculate the net profit made each week, with net profit = Volume Sold x Price ...
Need Help
kentyler's avatar
kentyler
Icon for Solution Sage rankSolution Sage
6 years ago

I think you should split your promotions table into one table for segment promotions and one for brand promotions. Then each promotion table will have its own relationship and can be linked to sales or products or both...depending on what you want to report on.

 

 

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Unfortunately this wouldn't make my model robust enough:

    tomorrow I might want to split my promotions over a different variable (let's say size or flavour) and I would need to change the whole model.

     

    What I'm looking for is a solution in Power Query (M language) but I struggle with the language semantics.

     

    In pseudo-code I would need the following:

    Promotions.AddColumn (list of EAN)

    list of EAN = Contract.SelectRows(

    if(Promotions.Brand is not null) Contract.Brand == Promotions.Brand 

    AND if(Promotions.Segment is not null) Contract.Segment== Promotions.Segment

    AND if(Promotions.Format is not null) Contract.Format == Promotions.Format

    )

     

    Afterwards I can split the List into new Rows using 'Split Columns' and I can continue working on my model