Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
brenda89111
Frequent Visitor

Add a delta column in matrix

I’m trying to reproduce an Excel report in Power BI but I’m struggling to do so.

 

I have a matrix visual with 6 measures displayed as rows and a date column that is filtered to only show the last 3 years as column.

 

I would like to add a delta column for each year to show how the measure changed from the previous year.

 

Is there a way to accomplish this? Even if it is with a different visual?

 

Naamloos.jpg

6 REPLIES 6
Manoj_Nair
Solution Supplier
Solution Supplier

@brenda89111 I have created a sample dataset containing Account Head, Year and Amount as shown below. The solution is explained in the PBIX file, check the transformation steps in the Balance sheet (New) table. Hope this should give you an idea for fixing your problem.

 

Manoj_Nair_4-1669830569644.png

 

The PBIX file has all the details.

If this post helps, then please consider to Accept it as the solution.

 

 

NikhilChenna
Continued Contributor
Continued Contributor

Hi @brenda89111 , It is easy you have to make use of date add function and create a new measure like below, but make sure oyu have datedimenison table and create a one to many relationship from date table to your table.

 

1. Previousyear= CALCULATE( [measure1], DATEADD('DimDate'[Date], -1, Year ))

2. diff % = divide([measure2]-[Previousyear],[Previousyear])

and you can do the same for other years too.

 

Regards,

Nikhil Chenna

 

Appreciate with a Kudos!! (Click the Thumbs Up Button)
Did I answer your question? Mark my post as a solution!

Hi,

Thank you for your reply. Unfortunately does the creation of a measure not solve my problem, as this would result in an extra row in my matrix instead of an extra column. I would also have to create seperate timelagged features for each of my 6 KPIs.

amitchandak
Super User
Super User

@brenda89111 , with help from date table and time intelligence you can get year behind measure to get diff

 

Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),SAMEPERIODLASTYEAR('Date'[Date]))

 

diff = [This Year]-[Year behind Sales]
diff % = divide([This Year]-[Year behind Sales],[Year behind Sales])

 

But that will display for all values.

 

You can check these Hybrid display blogs

 

if you are looking for a Hybrid display with Matrix Column and measure
https://community.powerbi.com/t5/Community-Blog/Creating-a-custom-or-hybrid-matrix-in-PowerBI/ba-p/1...
https://community.powerbi.com/t5/Quick-Measures-Gallery/The-New-Hotness-Custom-Matrix-Hierarchy/m-p/...

vote for Hybrid Table
https://ideas.powerbi.com/ideas/idea/?ideaid=9bc32b23-1eb1-4e74-8b34-349887b37ebc

 

Time Intelligence, DATESMTD, DATESQTD, DATESYTD, Week On Week, Week Till Date, Custom Period on Period,
Custom Period till date: https://youtu.be/aU2aKbnHuWs&t=145s

Thanks for pointing me to the threat about the hybrid display of a matrix. This comes very close to what I am looking for, instead that I don't have a column (e.g. products) on row, but sepperate measures. This makes things even more complicated. Perhabs I need to create the view I need in the database instead of trying to do this in Power BI desktop. 

If you are still interested in looking to do this in Power BI, the custom/hybrid matrix method should still work with measures on rows. The only issue is you will need to code each measure to take the column layout into account.





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.