Using different data types on the same row in a matrix

[BUC]

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

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:

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.

[danextian]

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.

[BUC]

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 · Accepted solution]

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 visual

Toggle on the option "Switch values to rows"

Best Regards

[BUC]

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