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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
BUC
Frequent Visitor

Using different data types on the same row in a matrix

I have a client who has asked for a Power BI version of this spreadsheet, basically:

BUC_1-1723544228880.png

 

I've tried to replicate this using a Matrix, but I can't get YoY underneath 2023 and 2024 - I've had to do them as measures and then add them to the values field so essentially it's the same data, it just reads left to right instead of top to bottom:

BUC_0-1723544180138.png

 

However, the client is insisting they want the sequence of rows to be Year, then Year on Year, then Year on Year Percentage.  Is this actually possible?  I can't see how I can do it without writing some monstrously complicated DAX that's going to eat up memory and even then I'm dubious.

1 ACCEPTED SOLUTION
Anonymous
Not applicable

Hi @BUC ,

I created a sample pbix file(see the attachment), please check if that is what you want.

1. Create the measures as below

Current Year value = CALCULATE(SUM('Table'[Value]),YEAR('Table'[Date])=YEAR(TODAY()))
Previous year value = CALCULATE(SUM('Table'[Value]),YEAR('Table'[Date])=YEAR(TODAY())-1)
YoY = [Current Year value]-[Previous year value]
YoY% = DIVIDE([YoY],[Previous year value])

2. Create a matrix visual with the below Fields and format settings 

Create a matrix visualCreate a matrix visual

Toggle on the option "Switch values to rows"Toggle on the option "Switch values to rows"

Best Regards

View solution in original post

4 REPLIES 4
danextian
Super User
Super User

Hi @BUC ,

 

It won't requrie a monstrously complicated DAX but a disconnected table as a placeholder and some DAX measures and a custom format string to return either a number or a percentage. Attached are sample pbixes to help you get started.

 





Dane Belarmino | Microsoft MVP | Proud to be a Super User!

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


"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu or follow my page on Facebook @DAXJutsuPBI.
BUC
Frequent Visitor

Hi there, thanks for your speedy response 🙂

I'm not sure this is what I'm after; I may be missing something though (still relatively new to Power BI).

 

Using your example, what I'd need is an additional row where I've put an arrow that shows Jan 2020 - Jan 2021.  Is that possible?  I already have the difference calculations (both Sum and Percentage), I just need to find out if I can have these values as rows underneath the first two years (I'm only comparing current year to previous year so my table would only ever have two rows per item).

Anonymous
Not applicable

Hi @BUC ,

I created a sample pbix file(see the attachment), please check if that is what you want.

1. Create the measures as below

Current Year value = CALCULATE(SUM('Table'[Value]),YEAR('Table'[Date])=YEAR(TODAY()))
Previous year value = CALCULATE(SUM('Table'[Value]),YEAR('Table'[Date])=YEAR(TODAY())-1)
YoY = [Current Year value]-[Previous year value]
YoY% = DIVIDE([YoY],[Previous year value])

2. Create a matrix visual with the below Fields and format settings 

Create a matrix visualCreate a matrix visual

Toggle on the option "Switch values to rows"Toggle on the option "Switch values to rows"

Best Regards

BUC
Frequent Visitor

That's exactly what I need - you're a genius! Thanks so much 🙂

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

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

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

Top Kudoed Authors