Transform table using DAX

[Anonymous]

Hello!

I am very happy to get ideas on how to transform a table from source to target below using DAX.
-------------
EDIT
Logic for Sum levels:
Number of sum levels are 3 since number of unique rows for ParentRowId that has a value is 3 

Sum level 3: Rows ChildRowId PTR1000721 and PTR1000725 belongs to sum level 3 since they lack ParentRowId
Sum level 2: Rows PTR1000683, PTR1000722, PTR1000723, PTR1000696 and PTR1000724 belongs to sum level 2 since their ParentRowId belongs to sum level 3
Sum level 1: Rows PTR1000684, PTR1000689, PTR1000697, PTR1000706 and PTR1000726 belongs to sum level 1 since their ParentRowId belongs to sum level 2

Logic for Sort order:
I added Support column in order to try to show the logic for Sort order. It is based on a combinaiton of Row Id and Sum level.

Source:

Target:

Source in Table format

PlanTemplateRowId	PlanTemplateParentRowId	PlanTemplateRowText	SequenceNo
PTR1000721		EBIT	0
PTR1000683	PTR1000721	Gross profit	1
PTR1000684	PTR1000683	Net sales	2
PTR1000689	PTR1000683	Cost of sales	7
PTR1000722	PTR1000721	Gross margin	12
PTR1000723	PTR1000721		13
PTR1000696	PTR1000721	Operating expenses	14
PTR1000697	PTR1000696	Personnel expenses	15
PTR1000706	PTR1000696	Other expenses	24
PTR1000726	PTR1000696	Depreciation and amortization	34
PTR1000724	PTR1000721		37
PTR1000725		EBIT margin	38

Target in table format:

ChildRowId	ParentRowId	RowText	SequenceNo	Sum level	Text	Sort order	Support column
PTR1000684	PTR1000683	Net sales	2	1	Net sales	1	00.01.02
PTR1000689	PTR1000683	Cost of sales	7	1	Cost of sales	2	00.01.07
PTR1000683	PTR1000721	Gross profit	1	2	Gross profit	3	00.01.ZZ
PTR1000722	PTR1000721	Gross margin	12	2	Gross margin	4	00.12.ZZ
PTR1000723	PTR1000721		13	2		5	00.13.ZZ
PTR1000697	PTR1000696	Personnel expenses	15	1	Personnel expenses	6	00.14.15
PTR1000706	PTR1000696	Other expenses	24	1	Other expenses	7	00.14.24
PTR1000726	PTR1000696	Depreciation and amortization	34	1	Depreciation and amortization	8	00.14.34
PTR1000696	PTR1000721	Operating expenses	14	2	Operating expenses	9	00.14.ZZ
PTR1000724	PTR1000721		37	2		10	00.37.ZZ
PTR1000721		EBIT	0	3	EBIT	11	00.ZZ.ZZ
PTR1000725		EBIT margin	38	3	EBIT margin	12	38.ZZ.ZZ

[MFelix]

Hi @Anonymous ,

Add the following calculated columns:

Sum level = SWITCH( 'Table'[SequnceNo],
0, 3,
1, 2,
2, 1,
7, 1,
12, 2,
13, 2,
15, 1,
24, 1,
34, 1,
14, 2,
37,2,
38,3

)

Text = SWITCH( 'Table'[SequnceNo],
0,  "EBIT",
1, "Gross Profit",
2, "Net Sales",
7, "Cost of Sales",
12, "Gross Margin",
13, "",
15, "Personnel expenses",
24, "Other Expenses",
34, "Depreciation and amortization",
14, "Operating expenses",
37,"",
38,"EBIT Margin"

)

Sorting = SWITCH( 'Table'[SequnceNo],
2, 1,
7, 2,
1, 3,
12, 4,
13, 5,
15, 6,
24, 7,
34, 8,
14, 9,
37, 10,
0,11,
38,12

)

Added the sorting in order to get the correct presentation on he visuals, sort the text column by the sorting.

[Anonymous]

Thanks @MFelix... I just editeed to question above... I need to base the sum levels on the relation between Child and Parent row Ids since it may change...  

[MFelix]

Hi @Anonymous ,

How do you now the sorting order of the columns based on the parent child? there is an option to create a PATH function that would give you the values and each of the levels you need however when you have several values concurring at the same level how do you know the sorting?

[amitchandak]

@Anonymous , not able to get the logic, can you explain with example

[Anonymous]

@MFelix  @amitchandak  Thanks for taking your time... I tried to provide examples and explanations above

[MFelix]

Hi @Anonymous ,

I'm already abble to calculate the values on the Sum level based on the parent and child relation:

Sum Level =
VAR PathValue =
    PATH ( 'Table'[PlanTemplaRowID], 'Table'[PlanTemplateParentRowID] )
VAR Pathlengthvalue =
    PATHLENGTH ( PathValue )
RETURN
    SWITCH ( Pathlengthvalue, 1, 3, 2, 2, 3, 1 )

Only question that is remaining is the sorting, can it be done based on the seuquence number or not?Is the Sequence Nº is not a part of your original table? Asking this because on the first calculations I have send you refered that they could not be used.

[Anonymous]

Thanks for this answer. It is fine to idé SequenceNo as long as the solution take into consideration that the values may change. I added a support column which I thought could help to understand ☺️

[MFelix]

Hi @Anonymous ,

When you refer that the SequenceNo can change do you mean that it will change but you want the sorting to be the same? so in this case if for example EBIT changes from 0 to 55 you would still want it to keep it on the order 11?

Or does it mean a different thing? 

This questions come from the fact that to make a measure to calculate this order we need to have the logic similar. Altough I'm looking at the example you gave the only logic I see is only the sequence number however you say this can change, and on the rest of the values I do not see any logic of order being it alphabetic or number.

Once again sorry for all the questions but want to give you a solution that is scalabble and flexible.

[Anonymous]

The sequenceNo may change and then the sorting should reflect that change.

[MFelix]

When you reflect that should reflect is to keep it on the same sorting ?

Again EBIT row that has a sequence of 0 if it changes to 55 should keep the sorting order of 11 correct?

[Anonymous]

Thanks for not giving up on me 🙂
If the SequenceNo of  row "EBIT" changes from 0 to 55, The sorter order will change from 11 to 12 for tat particular row.
Because: The row "EBIT margin" has the sequenceno 38, which is less than 55 so that particular row the will get sort order 11 instead of 12. 

Hope it is not impossible to grasp