Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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...
Need Help
Tips and Tricks
  • az38's avatar
    az38
    6 years ago

    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

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Anonymous please use below measures

_cost = 
VAR _actual = CALCULATE(SUM('Table'[Cost]),FILTER(ALLEXCEPT('Table','Table'[Vessel],'Table'[Voyage]),'Table'[data Type]="Actual"))
VAR _estimate = CALCULATE(SUM('Table'[Cost]),FILTER(ALLEXCEPT('Table','Table'[Vessel],'Table'[Voyage]),'Table'[data Type]="Estimate"))
RETURN _actual-_estimate
_Revenue = 
VAR _actual = CALCULATE(SUM('Table'[Revenue]),FILTER(ALLEXCEPT('Table','Table'[Vessel],'Table'[Voyage]),'Table'[data Type]="Actual"))
VAR _estimate = CALCULATE(SUM('Table'[Revenue]),FILTER(ALLEXCEPT('Table','Table'[Vessel],'Table'[Voyage]),'Table'[data Type]="Estimate"))
RETURN _actual-_estimate
Anonymous's avatar
Anonymous
Not applicable
6 years ago

Hi Vimal,

 

Thanks for your solution. But after inserting, it does not give me the intended result either. Please advise.

 

I am expecting get a result like this: