Forum Discussion

BUC's avatar
BUC
Frequent Visitor
2 years ago
Solved

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:   I've tried to replicate this using a Matrix, but I can't get YoY underneath 2023 and 2024 - I've had to ...
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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's avatar
BUC
Frequent Visitor
2 years ago

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's avatar
Anonymous
Not applicable
2 years ago

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's avatar
    BUC
    Frequent Visitor
    2 years ago

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