Forum Discussion
Number table
Hi,
It may not be the prettiest way to do it but it works:
Assumptions I used:
- each Sale has a unique ID
- sales have values
- the sales table is called Table
So:
1. in Power query, create an Index Column starting at 1
2. then create a new Table as below:
Series = GENERATESERIES(3,COUNTROWS('Table')*3,3)This will generate a series starting with 3, and it will increment by 3 all the way to how many rows you have in sales.
3. create a calculated Index column in the Series table:
Index = RANKX(Series,Series[Value],,ASC,Dense)4. in Modeling, create a 1<->1 relationship between the Index in Table and Index in Series
5. in the sales Table, create a new column:
Multiples_3 = 'Table'[Value]*LOOKUPVALUE(Series[Value],Series[Index],'Table'[Index])See below some screenshots.
If this solves your problem then please mark it as the solution so others can see it.
Series table / Sales data Table
Sales in Multiples of 3 =
VAR Multiple = sum[Sales]
RETURN Multiple * 3
Is this what you are looking for? if no then please elaborate your requirement
If this helps you please give thumbs up and accept it as solution
Hello Khushidesai0109
for first row sales, it should be multiple of 3
for second row sales, its should be multiple of 6
for third row sales, it should be multiple of 9
.
. so on...
Hi,
It may not be the prettiest way to do it but it works:
Assumptions I used:
- each Sale has a unique ID
- sales have values
- the sales table is called Table
So:
1. in Power query, create an Index Column starting at 1
2. then create a new Table as below:
Series = GENERATESERIES(3,COUNTROWS('Table')*3,3)This will generate a series starting with 3, and it will increment by 3 all the way to how many rows you have in sales.
3. create a calculated Index column in the Series table:
Index = RANKX(Series,Series[Value],,ASC,Dense)4. in Modeling, create a 1<->1 relationship between the Index in Table and Index in Series
5. in the sales Table, create a new column:
Multiples_3 = 'Table'[Value]*LOOKUPVALUE(Series[Value],Series[Index],'Table'[Index])See below some screenshots.
If this solves your problem then please mark it as the solution so others can see it.
Series table / Sales data Table