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 t...
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

     

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Additional Info: 

 

Filter of quarter should be based here

So Q1, it should show October Target, Nov Target, Dec Target, however they are in text format. I want it as text. To filter down based if they are tagged as FY23 Q1

 

 

 

 

 

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

Hi, Anonymous 

According to your description, your Date column is a text type data, and your filter is Q1-Q4, but you say that Q1 stands for FY23Q1, so when I filter FY23Q1, which field do I calculate to determine the month is 10, 11, 12? Second, to your question, can you provide me with your sample data, as well as the fields you want to put in the slicer and the final data that you want to show in the visual? This way we can better understand your problem and help you solve it.

 

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi, 

     

    This is sample data

     

    DateValueNumberNameOwnerActualsRAGTarget
    Jul-22CREATE1Pipeline Coverage: Growth (%)KA Turner;
    J. Ieraci; P. Renders    		1RED250%
    Aug-22CREATE1Pipeline Coverage: Growth (%)KA Turner;
    J. Ieraci; P. Renders    		1GREEN250%
    Sep-22CREATE1Pipeline Coverage: Growth (%)KA Turner;
    J. Ieraci; P. Renders    		1 250%
    Oct-22CREATE1Pipeline Coverage: Growth (%)KA Turner;
    J. Ieraci; P. Renders    		1AMBER250%
    Nov-22CREATE1Pipeline Coverage: Growth (%)KA Turner;
    J. Ieraci; P. Renders    		1NONE250%
    Dec-22CREATE1Pipeline Coverage: Growth (%)KA Turner;
    J. Ieraci; P. Renders    		1 250%
    Jan-23CREATE1Pipeline Coverage: Growth (%)KA Turner;
    J. Ieraci; P. Renders    		1 250%
    FY23 Q1CREATE1Pipeline Coverage: Growth (%)KA Turner;
    J. Ieraci; P. Renders    		1 250%
    FY23 Q2CREATE1Pipeline Coverage: Growth (%)KA Turner;
    J. Ieraci; P. Renders    		1 250%

     

    So Q1 is FY23 Q1 from the sample dataset. I want to display Oct to Dec (three  columns) for actuals that is in text format

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Sorry, I know now what you mean. 

       

      FY Q1 is July to Sep

      FY Q2 is Oct to Dec

      Fiscal year 2023 started July 2022 to June 2023. 

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

      Hi , Anonymous 

      According to your description, i do not understand the " Oct to Dec (three  columns) for actuals that is in text format".

      Do you mean when i select the "Q1(FY23 Q1 )", it will show this value:

      I don't know if I understand correctly? Whether to select Q1, calculate [Date]="FY23 Q1" || [Date]="Oct-22" || [Date] = "Nov-22" || [Date]="Dec-22"?

       

      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

      • Anonymous's avatar
        Anonymous
        Not applicable
        3 years ago

        It should show Oct actuals, Nov actuals, and Dec actuals. Sorry it should not be FYQ1 because I am expecting three columns when I select Q1 filter.