Create Measures in matrix format from records in transactional format i.e. top down approach

[MosesDhas]

there is a table like below where files are appended based on sequential revisions.

Table view
File	Ident Code	descp	weight	Revision	Discipline
1	Ident Code_2		125.56	0	STR
1	Ident Code_3			0	STR
1	Ident Code_4			0	STR
File	Ident Code	descp	weight	Revision	Discipline
1	Ident Code_2		130	1	STR
1	Ident Code_3			1	STR
1	Ident Code_4			1	STR
1	Ident Code_5			1	STR

the output should be like below where for every ident_code the variance between 1st and 0 revision, 2nd and 1st revision should come. if there is a 3rd revision , then dynamically the variance between rev3 and rev 2 should come.

Output
Ident Code	Descip	Rev 0 Weight	Var Rev 1	Var Rev 2
Ident_Code_2		125.56	4.44

any help would be highly appreciated

[v-jiewu-msft]

Hi @MosesDhas ,

Based on my testing, please try the following methods:

1.Create the simple table.

2.Create the new measure to calculate Rev 0 Weight.

Rev 0 Weight = 
VAR Ident_ = SELECTEDVALUE('Table'[Ident Code])
RETURN
CALCULATE(
    SUM('Table'[descp weight]),
    'Table'[Revision] = 0,
    'Table'[Ident Code] = Ident_
)

3.Create the new measure to calculate Variation Rev 1 and Rev 2.

Variation Rev 1 = 
VAR Ident_c = SELECTEDVALUE('Table'[Ident Code])
RETURN
CALCULATE(
    SUM('Table'[descp weight]),
    'Table'[Revision] = 1,
    'Table'[Ident Code] = Ident_c
) - [Rev 0 Weight]

Variation Rev 2 = 
VAR Iden_ = SELECTEDVALUE('Table'[Ident Code])
var result = CALCULATE(
    SUM('Table'[descp weight]),
    'Table'[Revision] = 2,
    'Table'[Ident Code] = Iden_
) - [Variation Rev 1]
RETURN
IF(MAX('Table'[Revision]) = 2, result ,BLANK())

4.Drag two measure into the table visual. The result is shown below.

Best Regards,

Wisdom Wu

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

[MosesDhas]

thanks alot for this, however there will be many revisions and the formula should be dynamic to add new revisions and on the table view we should be able to select what revisions need to be displayed.