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 me know if any part is not clear. Happy to explain further.

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

10 Replies

  • az38's avatar
    az38
    Community Champion
    6 years ago

    Hi Anonymous 

    try 2 measures

    RevenueMeasure = calculate(SUM(Table1[Revenue]);Table1[Data Type]="Actual")-calculate(SUM(Table1[Revenue]);Table1[Data Type]="Estimate")
CostMeasure = calculate(SUM(Table1[Cost]);Table1[Data Type]="Actual")-calculate(SUM(Table1[Cost]);Table1[Data Type]="Estimate")

     

    do not hesitate to give a kudo to useful posts and mark solutions as solution

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      I tried that before, but putting it into the matrix table will result in the difference appearing within each subtype, which is not whtat I want.

       

      • az38's avatar
        az38
        Community Champion
        6 years ago

        Anonymous 

        create 4 more measures

         

        CostEstimate = calculate(SUM(Table1[Cost]);Table1[Data Type]="Estimate")
CostActual = calculate(SUM(Table1[Cost]);Table1[Data Type]="Actual")
RevenueActual = calculate(SUM(Table1[Revenue]);Table1[Data Type]="Actual")
RevenueEstimate = calculate(SUM(Table1[Revenue]);Table1[Data Type]="Estimate")

         

        then

         

        RevenueDifference = [RevenueActual]-[RevenueEstimate]
CostDifference = [CostActual]-[CostEstimate]

         

        then visual

         

        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: