Forum Discussion
Create Table with Escalating Values
I need to create a table where one column goes 1 to 1000
The other column starts at 500 for the first 2 entries, and then increases by 500 and stays for the next 3 entries, and that continues for the rest of the table.
| Occurrence | Penalty |
| 1 | 500 |
| 2 | 500 |
| 3 | 1000 |
| 4 | 1000 |
| 5 | 1000 |
| 6 | 1500 |
| 7 | 1500 |
| 8 | 1500 |
| 9 | 2000 |
| 10 | 2000 |
| 11 | 2000 |
| 12 | 2500 |
| 13 | 2500 |
| 14 | 2500 |
and continuing to add 500 for the next 3 until the table ends
Hii bgierwi2
You need a sequence where the "Penalty" increment happens at irregular intervals:
- Step 1: Occurrences 1–2 (2 items) = 500
- Step 2: Occurrences 3–5 (3 items) = 1000
- Step 3: Occurrences 6–8 (3 items) = 1500
- And so on...
To Fix It
Go to the Modeling tab, click New Table, and paste the following code:
PenaltyTable = VAR BaseTable = GENERATESERIES(1, 1000, 1) -- Creates Occurrence 1 to 1000 RETURN SELECTCOLUMNS( BaseTable, "Occurrence", [Value], "Penalty", VAR _ID = [Value] RETURN IF( _ID <= 2, 500, -- This logic handles the "every 3 entries" shift after the first 2 500 + (INT(DIVIDE(_ID - 3, 3)) + 1) * 500 ) )Summary for the Community
By using INT(DIVIDE([Value] - Offset, Step)), you can create custom "stepped" sequences that don't follow a standard 1:1 increment.
If this DAX table correctly generates your penalty schedule, please mark this as the "Accepted Solution"!
3 Replies
Hii bgierwi2
You need a sequence where the "Penalty" increment happens at irregular intervals:
- Step 1: Occurrences 1–2 (2 items) = 500
- Step 2: Occurrences 3–5 (3 items) = 1000
- Step 3: Occurrences 6–8 (3 items) = 1500
- And so on...
To Fix It
Go to the Modeling tab, click New Table, and paste the following code:
PenaltyTable = VAR BaseTable = GENERATESERIES(1, 1000, 1) -- Creates Occurrence 1 to 1000 RETURN SELECTCOLUMNS( BaseTable, "Occurrence", [Value], "Penalty", VAR _ID = [Value] RETURN IF( _ID <= 2, 500, -- This logic handles the "every 3 entries" shift after the first 2 500 + (INT(DIVIDE(_ID - 3, 3)) + 1) * 500 ) )Summary for the Community
By using INT(DIVIDE([Value] - Offset, Step)), you can create custom "stepped" sequences that don't follow a standard 1:1 increment.
If this DAX table correctly generates your penalty schedule, please mark this as the "Accepted Solution"!
That worked perfectly for DAX.
Thank you
How similiar would the code be if I wanted to do it in Power Query?
