Forum Discussion
Dynamic Columns in a Matrix
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
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.
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
Thank you. This is exactly what I am looking for.
I cannot use the sum function because the requirement is the ACTUAL field should be in text format
Also,
The date2 should be in the values column.
It should appear as this if Q1 is selected:
July Actual July Target Aug Actua Aug Target Sep Actual Sep Target 1 1 2 2 3 3 5 5 6 6 7 7 Hi , Anonymous
I can not understand why if Q1 is selected:
July Actual July Target Aug Actua Aug Target Sep Actual Sep Target 1 1 2 2 3 3 5 5 6 6 7 7 In the previous , you said that if Q1 is selected, it shows the Month is in {10,11,12}.
And i am not sure what you want in the end now.
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
Sorry it should be Oct Nov December. My bad.
I want it like this if I select Q1
Oct Actual Oct Target Nov Actua Nov Target Dec Actual Dec Target 1 1 2 2 3 3 5 5 6 6 7 7 If I select Q4 like this:
July Actual July Target Aug Actua Aug Target Sep Actual Sep Target 1 1 2 2 3 3 5 5 6 6 7 7
Hi , Anonymous
I now agree with you on the display of columns, but I don't quite understand, how is your value calculated?
The data you arrived at was given based on the data you provided? Or is it given through my data?
And for what you said above, ACTUAL, is it text format? So what do you want to calculate in the end?
If convenient, can you give me a data that you ultimately want based on the test data I provided?
For your needs, I don't quite understand how to find the value you want.This is my test data:
DATEVALUE CHAINMETRIC NUMBERACTUALTARGET
1/1/2023 BILL 1 1 2 10/1/2022 BILL 1 2 3 11/1/2022 BILL 1 3 4 12/1/2022 BILL 1 4 5 2/1/2023 BILL 1 5 6 3/1/2023 BILL 1 6 7 4/1/2023 BILL 1 7 8 5/1/2023 BILL 1 8 9 6/1/2023 BILL 1 9 10 7/1/2022 BILL 1 10 11 8/1/2022 BILL 1 11 12 9/1/2022 BILL 1 12 13 1/1/2023 TEST 2 2 5 10/1/2022 TEST 2 3 6 11/1/2022 TEST 2 4 7 12/1/2022 TEST 2 5 8 2/1/2023 TEST 2 6 9 3/1/2023 TEST 2 7 10 4/1/2023 TEST 2 8 11 5/1/2023 TEST 2 9 12 6/1/2023 TEST 2 10 13 7/1/2022 TEST 2 11 14 8/1/2022 TEST 2 12 15 9/1/2022 TEST 2 13 16 FY23 Q1 BILL 1 100 120 FY23 Q2 BILL 1 110 130 FY23 Q3 BILL 1 120 145 FY23 Q4 BILL 1 130 140 FY23 YTD BILL 1 300 400 FY23 Q1 TEST 2 100 120 FY23 Q2 TEST 2 110 130 FY23 Q3 TEST 2 120 145 FY23 Q4 TEST 2 130 140 FY23 YTD TEST 2 300 400 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
The data you arrived at was given based on the data you provided?
-> Yes, it was just dummy data to represent my goal.
The ACTUAL field is in text format, so I only want to get any value from the actuals because it is in text format. So I understand your concern that we cannot sum it. I just want to know how to display the columns.
This was my formula to get compute the VALUE field since ACTUAL is in text format:
IF( SELECTEDVALUE('Slicer'[Quarter])= "Q1" ,CALCULATE(MAX('Scorecard Detail'[ACTUAL]),FILTER('Scorecard Detail',MONTH('Scorecard Detail'[Date2]) IN {10,11,12}) ) , IF( SELECTEDVALUE('Slicer'[Quarter])= "Q2" ,CALCULATE(MAX('Scorecard Detail'[ACTUAL]),FILTER('Scorecard Detail',MONTH('Scorecard Detail'[Date2]) IN {1,2,3}) ) ,IF(SELECTEDVALUE('Slicer'[Quarter])= "Q3" ,CALCULATE(MAX('Scorecard Detail'[ACTUAL]),FILTER('Scorecard Detail',MONTH('Scorecard Detail'[Date2]) IN {4,5,6}) ) ,IF(SELECTEDVALUE('Slicer'[Quarter])= "Q4" ,CALCULATE(MAX('Scorecard Detail'[ACTUAL]),FILTER('Scorecard Detail',MONTH('Scorecard Detail'[Date2]) IN {7,8,9}) ) , MAX('Scorecard Detail'[ACTUAL] )))))Hi , Anonymous
So , now we need to resolve the "The ACTUAL field is in text format" question?
For this question, I wonder why your ACTUAL is of type text, and why can't it be converted to data of type Number?
Second, if you can't convert, what does your data look like in Actual?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
Hi.
It is now converted into numeric, sorry the data was corrected on the backend. Can you provide now the dax formula to be able to show this?
I want it like this if I select Q1
Oct Actual Oct Target Nov Actua Nov Target Dec Actual Dec Target 1 1 2 2 3 3 5 5 6 6 7 7 If I select Q4 like this:
July Actual July Target Aug Actual Aug Target Sep Actual Sep Target 1 1 2 2 3 3 5 5 6 6 7 7
Hi , Anonymous
For this need , i realize it in the "12th" post in the past review.
When i select the Q1, it shows this:
When i select the Q4, it shows this:
And your current Progressive field has also been modified to Muber type, is there anything that does not match your needs? Or did I not understand your needs correctly?
Secondly, I am not very clear whether your monthly data and my calculation logic are the same, can you use the test data I provided to explain the calculation logic you want.
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
Can we just use dummy data so that it does not get complicated? I just want a dax formula that will show 6 columns in a matrix like this if I select a quarter from the slicer. It will display different set of six columns again if I select a different quarter.
Oct Actual Oct Target Nov Actual Nov Target Dec Actual Dec Target 1 1 2 2 3 3 5 5 6 6 7 7 The dax formula you provided is close, but I need it on the value field, not the column field. Can you share a dax formula using this data?
I just want a dax that will make the dynamic columns (in red) depending on the quarter selected
That quarter filter will only affect column B to G depending on what you select on the filter.
Column B to E should dynamically change and should be in the values not in the columns.
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
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
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.