cancel
Showing results for
Did you mean:
Frequent Visitor

## 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.

1 ACCEPTED SOLUTION
Super User IV

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

---------------------------------------

@ me in replies or I'll lose your thread!!!

##### I have a NEW book! DAX Cookbook from Packt
Over 120 DAX Recipes!

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

4 REPLIES 4
Super User IV

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

---------------------------------------

@ me in replies or I'll lose your thread!!!

##### I have a NEW book! DAX Cookbook from Packt
Over 120 DAX Recipes!

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Solution Sage

@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")```

Super User IV

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

---------------------------------------

@ me in replies or I'll lose your thread!!!

##### I have a NEW book! DAX Cookbook from Packt
Over 120 DAX Recipes!

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Solution Sage

Ha, nothing wrong with that! I just thought I might be missing out on something cool. Thanks!

Announcements

#### 2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.