Asymmetric Matrix Table in PowerBI

[leongramm99]

Hi guys! 

I have this Matrix table, with custom calculations for growth rate YoY and a CAGR calculation. I'd like to only show these values for the year 2022 and 2023, is there a way how this can be done within PowerBI? Either trough a filter function or if statement? 

Thank you so much in advance!

[leongramm99]

To clarify I'm looking for a table like this 

[BiNavPete · Accepted solution]

Hi @leongramm99 

Apologies, it wasn't clear form your first post what you wanted to see.
See fresh PBIX here.

The approach here is a little different because you don't have a dimension table with the growth columns.
So you have to use a disconnected table and then write a measure (in the example {Header Measure]) to manage what to do with each header in the disconnected table. This is done with a simple SWITCH statement.

The example assumes you want % growth amounts. This adds a formatting complication because you need % on the last two columns. So the measure is dynamically formatted in the ribbon with the functionality introduced here.

The example PBIX hard codes the year. But it may be possible to build the header table in PQ using data form the sales table and adjust the DAX with variables.

Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Happy to help!!

Pete
Web: https://binavigation.com
Linked In: https://www.linkedin.com/in/pete-smith-955b73181

[BiNavPete]

Hi @leongramm99 

See PBIX here.

Filtering just the two years is done in the filter pane with TOPN 2 max of year. Then when we move to 2024 the matrix will show 2023 and 2024.

YOY growth is achieved with a CALCULATE function and time intelligense logic using SAMEPERIODLASTYEAR

CAGR 3 years is a bit trickier:
1. Create a variable vTime and make it equal to 3. This gives you flexibility to copy the measure and change the 3 to another number to give CAGR over a different timespan.
2. Calculate Historic base sales with CALCULATE filtering ALL dates (to ignore the context on the visual) where the date is 3 years previously.
3. Having derived the variables/elements for the CAGR formula, pull this together using the DAX POWER function and manage the early years to return blank

Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Happy to help!!

Pete
Web: https://binavigation.com
Linked In: https://www.linkedin.com/in/pete-smith-955b73181

[ThomasWeppler]

Have you tried to use the filters to the right?

 Just add the years to the filter menu to the right and select the years you want to show