Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Dynamic Columns in a Matrix

Hi,

 

I need help to dynamically change the quarter columns in the matrix. Filter from above quarters will affect the highlighted columns such that if Q1, the table will show Oct to Dec. Q2 = Jan to Mar, etc. 

 

 

Need Help
Show and Tell
Tips and Tricks
  • v-yueyunzh-msft's avatar
    v-yueyunzh-msft
    3 years ago

    Hi ， Anonymous 

    Thank you for your quick reponse!

    According to your description, you want to "show six column not three". Right?

    For your needs, we need to create separate tables for the columns to implement your needs.

    Here are the steps you can refer to :
    (1)We need to click "New Table" and enter this:

    Table 2 = ADDCOLUMNS( ADDCOLUMNS( CROSSJOIN( ADDCOLUMNS( FILTER( ALL('Table'[Date2]) , [Date2]<>BLANK()) ,"Month" , FORMAT( [Date2] , "mmmm")) ,{"ACTUAL","TARGET"}) , "Column NAme" , [Month]&"  "&[Value]) , "Index" , SWITCH( TRUE() , MONTH([Date2]) =1 ,1 , MONTH([Date2]) =2 ,2, MONTH([Date2]) =3,3, MONTH([Date2]) =4,4,MONTH([Date2]) =5,5,MONTH([Date2]) =6,6,MONTH([Date2]) =7,7,MONTH([Date2]) =8,8,MONTH([Date2]) =9,9,MONTH([Date2]) =10,10,MONTH([Date2]) =11,11,MONTH([Date2]) =12,12))

    And we do not make any relationships between tables.

    (2)We need to click "New Measure" and enter this:

    Measure = 
var _value = 
 IF(MAX('Table 2'[Value])="ACTUAL", CALCULATE( SUM('Table'[ACTUAL]) ,TREATAS( VALUES('Table 2'[Date2]) ,'Table'[Date2])),CALCULATE( SUM('Table'[TARGET]) ,TREATAS( VALUES('Table 2'[Date2]) ,'Table'[Date2])))
 return
IF(ISFILTERED('Slicer'[Quarter]), IF(SELECTEDVALUE('Slicer'[Quarter])="Q1" && MAX('Table 2'[Index]) in {10,11,12} , _value ,  IF(SELECTEDVALUE('Slicer'[Quarter])="Q2" && MAX('Table 2'[Index]) in {1,2,3} , _value , IF(SELECTEDVALUE('Slicer'[Quarter])="Q3" && MAX('Table 2'[Index]) in {4,5,6} , _value , IF(SELECTEDVALUE('Slicer'[Quarter])="Q4" && MAX('Table 2'[Index]) in {7,8,9} , _value  )))) , _value)

    (3)Then we can make the [Column Name] field sort by the [Index] column:

    (4)Then we put the measure and the field we need on the visual and we will meet your need :

    When i select Q1, the result is as follows:

    Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

     

    Best Regards,

    Aniya Zhang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

     

27 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    3 years ago

    Anonymous Sorry, having trouble following, can you post sample data as text and expected output?
    Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

    Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

    The most important parts are:
    1. Sample data as text, use the table tool in the editing bar
    2. Expected output from sample data
    3. Explanation in words of how to get from 1. to 2.

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Hi.

       

      Sorry if it was vague.

       

      Q1, Q2, Q3, Q4 above are values of the filter dropdown above the table. It is a filter that I can select quarters on.

       

      What I want to achieve are the following:

       

      If I select Q1 from quarter filter above the table , Oct Nov Dec columns should appear. It contains sum of sales from Oct to December

       

       

      If I select Q2 from quarter filter above the table, January to March sales should appear

       

      Q3 : April to June 

       

      and so on..

      Note that the columns after the highlighted ones should be fixed no matter what I select from the dropdown filter above the table. 

       

       

       

       

       

      • v-yueyunzh-msft's avatar
        v-yueyunzh-msft
        Community Support
        3 years ago

        Hi , Anonymous 

        According to your description, you want to "Q1, Q2, Q3, Q4 above are values of the filter dropdown above the table.".

        Here are the steps you can refer to :

        (1)This is my test data:

        (2)We need to create a table as slicer, and we do not create relationship between two tables:

        (3)Then we need to create a measure:

        Value = var _slicer = SELECTEDVALUE('Slicer'[Quarter])
return
IF(_slicer ="Q1" , CALCULATE(SUM('Table'[Value]),FILTER('Table',MONTH('Table'[Date]) in {10,11,12})), IF(_slicer ="Q2" , CALCULATE(SUM('Table'[Value]),FILTER('Table',MONTH('Table'[Date]) in {1,2,3})),IF(_slicer ="Q3" , CALCULATE(SUM('Table'[Value]),FILTER('Table',MONTH('Table'[Date]) in {4,5,6})),IF(_slicer ="Q4" , CALCULATE(SUM('Table'[Value]),FILTER('Table',MONTH('Table'[Date]) in {7,8,9})) ,SUM('Table'[Value])    ))))

        (4)Then we put the [Quarter] on the slicer visual and the filed we need on the table and we will meet your need , the result is as follows:

         

        If this method does not meet your needs, you can provide us with your special sample data and the desired output sample data in the form of tables, so that we can better help you solve the problem.

        Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

         

        Best Regards,

        Aniya Zhang

        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly