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

[mt_729]

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.

[AlienSx · Accepted solution]

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]

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.