cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
burak
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:

UPLOAD.JPG

 

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.

upload2.JPG

1 ACCEPTED SOLUTION
Greg_Deckler
Super User IV
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!




View solution in original post

4 REPLIES 4
Greg_Deckler
Super User IV
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!




View solution in original post

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

 

Table.PNG

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!




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

Helpful resources

Announcements
MBAS on Demand

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.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Top Solution Authors
Top Kudoed Authors