Forum Discussion
Difference between 2 sub columns in a matrix
I have a matrix broken shown as such: I would like to create a difference column that would look like this in excel pivot table: This is how the raw data looks like: Let...
Anonymous
i got it
duplicate data source Table1 as 'Table 1 (2)'
then create new table
New Table = union('Table1';summarize('Table1 (2)';'Table1 (2)'[Vessel];'Table1 (2)'[Voyage];"Data Type";"zDifference";"Revenue";sumx('Table1 (2)';(calculate(SUM('Table1 (2)'[Revenue]);'Table1 (2)'[Data Type]="Actual")-calculate(SUM('Table1 (2)'[Revenue]);'Table1 (2)'[Data Type]="Estimate")));"Cost";sumx('Table1 (2)';(calculate(SUM('Table1 (2)'[Cost]);'Table1 (2)'[Data Type]="Actual")-calculate(SUM('Table1 (2)'[Cost]);'Table1 (2)'[Data Type]="Estimate")))))aaand
all fields are from New Table, of course
do not hesitate to give a kudo to useful posts and mark solutions as solution
Hi az38 ,
I understand your solution, so you're suggesting leveling the hierarchy of datatype into the same level as the measures so as to create direct simple subtraction.
That can work but visually will not look as structured as I intended. Is there no other way to obtain the way I like in the excel output?
Anonymous
i got it
duplicate data source Table1 as 'Table 1 (2)'
then create new table
New Table = union('Table1';summarize('Table1 (2)';'Table1 (2)'[Vessel];'Table1 (2)'[Voyage];"Data Type";"zDifference";"Revenue";sumx('Table1 (2)';(calculate(SUM('Table1 (2)'[Revenue]);'Table1 (2)'[Data Type]="Actual")-calculate(SUM('Table1 (2)'[Revenue]);'Table1 (2)'[Data Type]="Estimate")));"Cost";sumx('Table1 (2)';(calculate(SUM('Table1 (2)'[Cost]);'Table1 (2)'[Data Type]="Actual")-calculate(SUM('Table1 (2)'[Cost]);'Table1 (2)'[Data Type]="Estimate")))))
aaand
all fields are from New Table, of course
do not hesitate to give a kudo to useful posts and mark solutions as solution
