Forum Discussion

burak's avatar
burak
Frequent Visitor
9 years ago
Solved

Difference between 2 columns in Matrix or Table

 

I have read some solutions about this issue, but all of them was about the difference between date. I could not run any formula about my issue.

 

My sample data is below:

 

Is it possible to create a table or matrix in PBI looking like the following image? Currently, I am creating this table in Excel outside the pivot table, then import to the PBI. It takes so much time. If you help me, I will be more than happy.

Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    9 years ago

    Using an Enter Data query, I created a table with the following values:

    • S14
    • S15
    • S16

    Relate this table to your other table. I then created the following measures:

     

    SumUnitsTaken = SUMX(RELATEDTABLE(College),'College'[Unit Taken])

S15 vs S14 = CALCULATE([SumUnitsTaken],College[Term] = "S15") - CALCULATE([SumUnitsTaken],College[Term] = "S14")

S16 vs S15 = CALCULATE([SumUnitsTaken],College[Term] = "S16") - CALCULATE([SumUnitsTaken],College[Term] = "S15")

    Create a table with College, S15 vs S14 and S16 vs S15

4 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    9 years ago

    Using an Enter Data query, I created a table with the following values:

    • S14
    • S15
    • S16

    Relate this table to your other table. I then created the following measures:

     

    SumUnitsTaken = SUMX(RELATEDTABLE(College),'College'[Unit Taken])

S15 vs S14 = CALCULATE([SumUnitsTaken],College[Term] = "S15") - CALCULATE([SumUnitsTaken],College[Term] = "S14")

S16 vs S15 = CALCULATE([SumUnitsTaken],College[Term] = "S16") - CALCULATE([SumUnitsTaken],College[Term] = "S15")

    Create a table with College, S15 vs S14 and S16 vs S15

    • KGrice's avatar
      KGrice
      Memorable Member
      9 years ago

      Greg_Deckler For my own learning, can I ask the purpose of the Enter Data table you used? I got to the same end result of the desired matrix with just the three measures:

       

      SumUnitsTaken = SUM(TableName[Unit Taken])

S15 vs S14 = CALCULATE([SumUnitsTaken], TableName[Term]="S15") - CALCULATE([SumUnitsTaken], TableName[Term]="S14")

S16 vs S15 = CALCULATE([SumUnitsTaken], TableName[Term]="S16") - CALCULATE([SumUnitsTaken], TableName[Term]="S15")

       

      • Greg_Deckler's avatar
        Greg_Deckler
        Community Champion
        9 years ago

        Probably because I'm old school and still think in star schemas and snowflake schemas and fact and dimension tables. Old habits die hard.