Assign specific value to repeating blanks in a column based on the order they're in

[jmdaily83]

Hello - I have a table similiar to the sample table below.  The subtotal1/subtotal2/total does not pull in with my query due to the way the files are structured and pulled in.  The blanks will always be in the same order for each project but not necessarily in the same amount of rows from the previous section. (ie subtotal 1 could have 15 rows above it in Project A and 25 above it in Project B). I need to create a new column that assigns subtotal1/subtotal2/total based on these blanks.  

I have tried this custom column but it does not work, it just spins & spins and never even shows an error.  

= Table.AddColumn(#"Added Index", "Custom", each let

__Project = [Project Number],

__Index = [Index],

__Table = Table.SelectRows( #"Added Custom", each ([Project Number] = __Project and [Index] > __Index ) )

in

  Table.RowCount(__Table) + 1)

Project	kWh Category	Item Category	Item Type	$	New KWH Category
A	Engineering	BESS Engineering	Electrical	100	Engineering
A	MBOM Material	BESS Material	BESS Material	200	MBOM Material
A	BOP Material	Electrical BOP Material	Aux Material	300	BOP Material
A	EPC BOP	BOP Civil	Civil	400	EPC BOP
A	Substation	Substation	Substation Engineering	500	Substation
A	Transmission Line	Transmission Line	Transmission Line	600	Transmission Line
A	Substation	Substation	Substation	700	Substation
A	Freight	Freight	MBOM+BOP Freight	800	Freight
A	EPC BOP	Other	Insurance	900	EPC BOP
A	MBOM Material	BESS Material	Warranties	1000	MBOM Material
A	Project Services	Site Labour	Site Labour	1100	Project Services
A				6600	Subtotal 1
A	EPC BOP	Other	Permitting	1300	EPC BOP
A	Contingency	Sourcing Contingency	Sourcing Contingency	1400	Contingency
A				9300	Subtotal 2
A	Sales Tax	Sales Tax	Sales Tax	1600	Sales Tax
A	Bonding	Bonding	Bonding	1700	Bonding
A				12600	Total
B	Engineering	BESS Engineering	Electrical	100	Engineering
B	MBOM Material	BESS Material	BESS Material	200	MBOM Material
B	BOP Material	Electrical BOP Material	Aux Material	300	BOP Material
B	EPC BOP	BOP Electrical	BESS  Site Commissioning	400	EPC BOP
B	Substation	Substation	Substation Engineering	500	Substation
B	Transmission Line	Transmission Line	Transmission Line	600	Transmission Line
B	Substation	Substation	Substation Commissioning	700	Substation
B	Freight	Freight	MBOM+BOP Freight	800	Freight
B	EPC BOP	Other	Insurance	900	EPC BOP
B	MBOM Material	BESS Material	Warranties	1000	MBOM Material
B	Project Services	Site Labour	Site Labour	1100	Project Services
B				6600	Subtotal 1
B	EPC BOP	Other	Permitting	1200	EPC BOP
B	Contingency	Sourcing Contingency	Sourcing Contingency	1300	Contingency
B	Contingency	Risk Contingency	Risk Contingency	1400	Contingency
B				10500	Subtotal 2
B	Sales Tax	Sales Tax	Sales Tax	1500	Sales Tax
B	Bonding	Bonding	Bonding	1600	Bonding
B				13600	Total

[jmdaily83]

The end user ended up adjusting the way things were labeled on their end so I no longer needed to do anything custom on my end. 

thank you all for your help

[Anonymous]

Hi @jmdaily83 ,

You'd better add a level2 column in you table to dispaly your second level. And it's best to determine this level and import it together before importing, which is more convenient.

Column = CALCULATE(SUM('Table'[$]),FILTER('Table',[Project]=EARLIER('Table'[Project])&&[Level2]<=EARLIER('Table'[Level2])))

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Ashish_Mathur]

Hi,

Not sure of what you want but you should never create subtoal/total rows in any table.  These summarisation rows get created automatically when you create Table/matrix visuals.

[jmdaily83]

@Ashish_Mathur Thank you for your response. That's how I would typically do it, but in this particular visual the subtotals need to add together and this does not happen naturally in a matrix. So I need Subtotal 1, Subtotal 2 = subtotal 1 + subtotal 2, total = item A + item B + Subtotal 1 + Subtotal 2

Current matrix functionality

What I need - (notice no subtotal 3) and subtotals are added together