Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Be one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now

Reply
Rakesh_508
Helper I
Helper I

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 of 6

for third row multiple of 9 like so on....

 

second requirement is to like, need to generate 3 or any number table in power bi it can be static or dynamic..

 

Need your help on calculating/generating a number table in power bi like 

3*1=3

3*2=6

3*3=9

.

.

.

3*10=30 

 

Hope you got the requirement..

 

Thanks in advance..

1 ACCEPTED SOLUTION

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

Number table_1.jpgNumber table_2.jpg

 

 

 



If the post helped then please give it a Kudos and mark it as the solution so others can see it.
Cheers,

View solution in original post

6 REPLIES 6
Khushidesai0109
Resolver I
Resolver I

Hiii

you can create an indexed column so there you'll get 1,2,3 and soo on as per row
now add a calculated column and multiply that calculated column with 3 and you'll get an output as 3,6,9 and soo on
now create a Dax and multiply sum of sales with that calculated column
you will def get the desired output

if this post helps you out please give thumbs up and accept it as solution!!

Uzi2019
Super User
Super User

hi @Rakesh_508 
can you specify what data do you have ??
or want to generate a complete series that is fixed or it should be dynamic?? 

 

 

Don't forget to give thumbs up and accept this as a solution if it helped you!!!

Hello @Uzi2019 

 

Thanks for the reply..

 

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 of 6

for third row multiple of 9 like so on....

 

second requirement is to like, need to generate 3 or any number table in power bi it can be static or dynamic..

 

Hope you got the requirement..

 

Thanks in advance..

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

Number table_1.jpgNumber table_2.jpg

 

 

 



If the post helped then please give it a Kudos and mark it as the solution so others can see it.
Cheers,

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.