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

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

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?

  • az38's avatar
    az38
    Community Champion
    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

      Hi az38 

       

      Yes I think this is what I am looking for and it should work for me!!

       

      Thanks so much for your help!

    • adhumal2's avatar
      adhumal2
      Helper III
      6 years ago

      Hi az38 , Anonymous 

       

      This is exactly I want to see in my solution . Can you please explain the 'New Table' concept in easier terms. I am quite new to PowerBI

  • az38's avatar
    az38
    Community Champion
    6 years ago

    Anonymous 

    i think it is the easiest

    but. you can dublicate your data source. make a group by vessel/voyage, calculate rev and cost difference, fill the field Data Type as "Difference" (or "zDifference" for correct cloumn ort), then union tables (initial and grouped), then add Data Type column in the matrix as columns. 

    wow. maybe you could understand, because im not sure that was good explanation of my idea 🙂

     

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