Forum Discussion

Rakesh_508's avatar
Rakesh_508
Helper I
2 years ago
Solved

Number table

Hello All,   I would say its a kind of 2 requirements.   First one is I do have sales data, I need to show the sales in multiples of 3 like for first row  mulitple of 3 for second row multiple ...
Need Help
  • MNedix's avatar
    MNedix
    2 years ago

    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

     

     

     

Rakesh_508's avatar
Rakesh_508
Helper I
2 years ago

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...

MNedix's avatar
MNedix
Solution Sage
2 years ago

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