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

     

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

Hi, Anonymous 

Accoeding to your description, the date in your table is the text type. Right?

Here are the steps you can refer to :

(1)This is my test data:

(2)We need to click “New Column” to create a calculated column to convert the [Date] column type to date type and ignore the “FY23 Qn”:

Date2 = IF( CONTAINSSTRING('Table'[DATE] , "FY" ) ,BLANK() , DATEVALUE('Table'[DATE]) )

(3)Then we also need to create a table as a slicer like this:

 

(4)Then we can click “New measure” to create two measures:

ACTUAL TEST = IF( SELECTEDVALUE('Slicer'[Quarter])= "Q1" ,CALCULATE(SUM('Table'[ACTUAL]),FILTER('Table',MONTH('Table'[Date2]) IN {10,11,12}) ) , IF( SELECTEDVALUE('Slicer'[Quarter])= "Q2" ,CALCULATE(SUM('Table'[ACTUAL]),FILTER('Table',MONTH('Table'[Date2]) IN {1,2,3}) ) ,IF(SELECTEDVALUE('Slicer'[Quarter])= "Q3" ,CALCULATE(SUM('Table'[ACTUAL]),FILTER('Table',MONTH('Table'[Date2]) IN {4,5,6}) ) ,IF(SELECTEDVALUE('Slicer'[Quarter])= "Q4" ,CALCULATE(SUM('Table'[ACTUAL]),FILTER('Table',MONTH('Table'[Date2]) IN {7,8,9}) ) , SUM('Table'[ACTUAL])       ))))
TARGET TEST = IF( SELECTEDVALUE('Slicer'[Quarter])= "Q1" ,CALCULATE(SUM('Table'[TARGET]),FILTER('Table',MONTH('Table'[Date2]) IN {10,11,12}) ) , IF( SELECTEDVALUE('Slicer'[Quarter])= "Q2" ,CALCULATE(SUM('Table'[TARGET]),FILTER('Table',MONTH('Table'[Date2]) IN {1,2,3}) ) ,IF(SELECTEDVALUE('Slicer'[Quarter])= "Q3" ,CALCULATE(SUM('Table'[TARGET]),FILTER('Table',MONTH('Table'[Date2]) IN {4,5,6}) ) ,IF(SELECTEDVALUE('Slicer'[Quarter])= "Q4" ,CALCULATE(SUM('Table'[TARGET]),FILTER('Table',MONTH('Table'[Date2]) IN {7,8,9}) ) , SUM('Table'[TARGET])       ))))

(5)Then we can put the [Quarter] on the silcer visual and the [Date2] and other fields we need on the visual and we will meet your need , the result is as follows:

We also need to uncheck the Blank in the [Date2] column in the “Filter on this visual” configuration.

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

I cannot use the sum function because the requirement is the ACTUAL field should be in text format