Forum Discussion

leongramm99's avatar
leongramm99
Frequent Visitor
3 years ago
Solved

Asymmetric Matrix Table in PowerBI

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!

 

 

Need Help
Tutorial Requests
  • BiNavPete's avatar
    BiNavPete
    3 years ago

    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





4 Replies

  • ThomasWeppler's avatar
    ThomasWeppler
    Icon for Impactful Individual rankImpactful Individual
    3 years ago

    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

  • BiNavPete's avatar
    BiNavPete
    Icon for Resolver III rankResolver III
    3 years ago

    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





    • BiNavPete's avatar
      BiNavPete
      Icon for Resolver III rankResolver III
      3 years ago

      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