Forum Discussion

mt_729's avatar
mt_729
Regular Visitor
2 years ago
Solved

Create a table based on two values with increments in Power Query

Hello,

 

I'm trying to create a dynamic table which will receive an input for minimum and maximum targets. The query should then add rows with 0.1 increments.

What I find difficult is that a column called "Payout" should correspond to the minimum Target and calculate increments until the maximum payout.

 

I've added a screenshot so it's easier to visualise. Basically, my inputs are the table below:

 

It should calculate the increment, (in this case, 1.5%) and create a column like this:

 

 

 

Note that past 98%, the payout is capped at 120%, as this represents the max.

 

Need Help
Show and Tell
Tips and Tricks
  • AlienSx's avatar
    AlienSx
    2 years ago

    hello, mt_729 

    let
    Source = your_table,
    gen = List.Generate(
        () => [Target = Source{[Type = "Minimum"]}[Target], Payout = Source{[Type = "Minimum"]}[Payout]],
        (x) => x[Target] <= Source{[Type = "Maximum"]}[Target],
        (x) => [Target = x[Target] + .001, Payout = List.Min({Source{[Type = "Maximum"]}[Payout], x[Payout] + .015})]
    ),
    z = Table.FromRecords(gen)
in
    z

2 Replies

  • AlienSx's avatar
    AlienSx
    Super User
    2 years ago

    hello, mt_729 

    let
    Source = your_table,
    gen = List.Generate(
        () => [Target = Source{[Type = "Minimum"]}[Target], Payout = Source{[Type = "Minimum"]}[Payout]],
        (x) => x[Target] <= Source{[Type = "Maximum"]}[Target],
        (x) => [Target = x[Target] + .001, Payout = List.Min({Source{[Type = "Maximum"]}[Payout], x[Payout] + .015})]
    ),
    z = Table.FromRecords(gen)
in
    z
  • rubayatyasmin's avatar
    rubayatyasmin
    Community Champion
    2 years ago

    Hi, mt_729 

     

    First, you need to create a basic table with your minimum and maximum target values. You can enter these directly in Power BI using "Enter Data" from the Home ribbon or import them from an external source. (I am guessing you have one already)

     

    * create a calculated table

     

    TargetTable =
    VAR MinTarget = 95.0
    VAR MaxTarget = 98.0
    VAR TargetIncrement = 0.1
    VAR MinPayout = 75
    VAR PayoutIncrement = 1.5
    VAR MaxPayout = 120
    RETURN
    GENERATESERIES(MinTarget, MaxTarget, TargetIncrement)

     

    this dax will create a single column table with values from 95.0 to 98.0 incremented by 0.1.

     

    Now add a calculated column in your newly created TargetTable

     

    Payout =
    VAR BasePayout = [Target] - 95.0
    RETURN
    IF(
    [Target] <= 98.0,
    MIN(MinPayout + (BasePayout * (PayoutIncrement / TargetIncrement)), MaxPayout),
    MaxPayout
    )

     

    Replace [Target] with the actual column name that contains the target values.