Forum Discussion

hansreivers's avatar
hansreivers
Helper I
1 year ago
Solved

Need help with with tabel Power Query

Hi all,   We've got an excel with all items. This file is being refreshed automatically from our database.  In our database, we have let's say 10 items with salesprice 500, costprice 500 and thus ...
Need Help
  • Akash_Varuna's avatar
    Akash_Varuna
    1 year ago

    Hi hansreivers TRy this 

    1. In Power Query, create a conditional column:

     

    Margin = if [Article] = "x" then [Costprice] * 0.15 else 0

     

    • In Power BI, add a calculated column:

     

    TotalPrice = [Salesprice] + [Margin]

     

    • Use a mapping table for dynamic updates, or a DAX measure:

     

    DynamicMargin = IF(SELECTEDVALUE('Table'[Article]) = "x", SUM('Table'[Costprice]) * 0.15, 0)

     

    If this post helped please do give a kuods and accept this as a solution
    Thanks In Advance 

  • Akash_Varuna's avatar
    Akash_Varuna
    1 year ago

    Hi hansreivers Then you can add a log table with Article, Effective Date, and Increment columns. Load it into Power BI and create a dynamic margin measure using DAX:

    DynamicMargin = 
VAR Increment = 
    CALCULATE(
        MAX('LogTable'[Increment]),
        FILTER(
            'LogTable',
            'LogTable'[Article] = 'SalesTable'[Article] &&
            'LogTable'[Effective Date] <= 'SalesTable'[Date]
        )
    )
RETURN 'SalesTable'[Costprice] * Increment

     

Akash_Varuna's avatar
Akash_Varuna
Super User
1 year ago

Hi hansreivers TRy this 

  1. In Power Query, create a conditional column:

 

Margin = if [Article] = "x" then [Costprice] * 0.15 else 0

 

  • In Power BI, add a calculated column:

 

TotalPrice = [Salesprice] + [Margin]

 

  • Use a mapping table for dynamic updates, or a DAX measure:

 

DynamicMargin = IF(SELECTEDVALUE('Table'[Article]) = "x", SUM('Table'[Costprice]) * 0.15, 0)

 

If this post helped please do give a kuods and accept this as a solution
Thanks In Advance 

  • hansreivers's avatar
    hansreivers
    Helper I
    1 year ago

    Hi Akash_Varuna and the community,

     

    That could work, but then I have to make a "manually measure" for all these exeptions and I want to have a sort of "log tabel" where I can / need to refer to, and I want to make this dynamically with one measure / column for the margin.

     

    When I for example for article X have 2 sales;

    01-05-24 --> the increment should be 15%

    01-05-25 --> the increment should be 17,50%

     

    Sale product Y dd 05-03-25 the increment should be 15% of € 350 because of the change in price per 01-01-25

     

    Thanks in advance,

    • Akash_Varuna's avatar
      Akash_Varuna
      Super User
      1 year ago

      Hi hansreivers Then you can add a log table with Article, Effective Date, and Increment columns. Load it into Power BI and create a dynamic margin measure using DAX:

      DynamicMargin = 
VAR Increment = 
    CALCULATE(
        MAX('LogTable'[Increment]),
        FILTER(
            'LogTable',
            'LogTable'[Article] = 'SalesTable'[Article] &&
            'LogTable'[Effective Date] <= 'SalesTable'[Date]
        )
    )
RETURN 'SalesTable'[Costprice] * Increment