Dynamic Columns in a Matrix

[Anonymous]

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. 

[Anonymous]

To make it clearer, 

What you provided is like:

My goal is:

I want to dynamically change the six columns not the three columns above that are merged. I hope this made sense

[v-yueyunzh-msft · Accepted solution]

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]

Hi,

I just realized that this is not 100% the desired result.

The COLUMN NAME field from the columns is the one that is dynamic.

I want it to be dynamic in the VALUES field. The 6 columns must be dynamic in the values section. We should not put anything in the COLUMNS area. 

It should be something like this but the 6 values should dynamically change.The measure should be in the VALUES area. 

The report also has other values aside from the 6 values BUT the slicer should only affect the first six values  as indicated by the red circle and not the other values in the table (after the circle). 

So two concerns:

1. There should be no entry in the COLUMN area. They should all be in the values and must be dynamic. It just looks like a column but it is not. They are all just VALUES for every entry of a row. (Like a straight table). 

2.The slicer should not affect the other values in the same matrix. 

[Anonymous]

Thank you. This is exactly what I am looking for. 

[Anonymous]

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

[Anonymous]

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. 

[Anonymous]

Thanks. This is exactly what I am looking for. It's just that I want that columns to be part of a different table in a different sheet. Do I need to connect the tables?