Price Range - PowerPivot

[tusharmehta]

Hello Everyone,

I herewith posting one example in which I have two tables:

1 Transaction table
2 Range Table

Transaction Table		Range Table
Sr. No	Product	Amount	Range		Range	Start	End
1	Pr1	100	??		0-100	0	100
2	Pr2	200	??		101-200	101	200
3	Pr3	150	??		201-300	201	300
4	Pr4	110	??		301-400	301	400
5	Pr5	300	??		401-500	401	500
6	Pr6	450	??		501-600	501	600
7	Pr7	122	??		601-700	601	700
8	Pr8	1000	??		701-800	701	800
9	Pr9	232	??		801-900	801	900
10	Pr10	265	??		901-1000	901	1000
					1001-1100	1001	1100
					1101-1200	1101	1200
					1201-1300	1201	1300

<tbody>
</tbody>

My questions is how to fill the Range from Range table to Transaction table automatically using PowerPivot calculate or other functions?

Thanks in Advance.

 

[Hashiru]

Hi Tusharmehta,

I have a alternative Power Query solution to your problem. Here are the steps

1. Load the Transaction table to Power Query.
a. Create a Blank Query using formula:
=Excel.CurrentWorkbook() or
b. Create new workbook and import the Transaction table to Power Query

2. Insert a Modulo with Amount of 100 using the formula:
=Table.AddColumn(#"Expanded Content", "Modulo", each Number.Mod([Amount], 100), type number)

3. Insert an Integer-Division with Amount of 100 using the formula:
=Table.AddColumn(#"Expanded Content", "Modulo", each Number.Mod([Amount], 100), type number)

4. Create a Lower band with the formula:
=Table.AddColumn(#"Expanded Content", "Modulo", each Number.Mod([Amount], 100), type number)

5. Create Upper band with the formular:
= Table.AddColumn(#"Lower Band", "Upper Band", each if [Modulo] > 0 then ([#"Integer-Division"]+1)*100 else [#"Integer-Division"]*100)

6. Create your band with the formular:
= Table.AddColumn(#"Upper Band", "Band", each Text.From([Lower Band]) & "-" & Text.From([Upper Band]))

Hope this helps