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
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.
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
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?
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
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
Hi,
This is sample data
Date Value Number Name Owner Actuals RAG Target Jul-22 CREATE 1 Pipeline Coverage: Growth (%) KA Turner;
J. Ieraci; P. Renders1 RED 250% Aug-22 CREATE 1 Pipeline Coverage: Growth (%) KA Turner;
J. Ieraci; P. Renders1 GREEN 250% Sep-22 CREATE 1 Pipeline Coverage: Growth (%) KA Turner;
J. Ieraci; P. Renders1 250% Oct-22 CREATE 1 Pipeline Coverage: Growth (%) KA Turner;
J. Ieraci; P. Renders1 AMBER 250% Nov-22 CREATE 1 Pipeline Coverage: Growth (%) KA Turner;
J. Ieraci; P. Renders1 NONE 250% Dec-22 CREATE 1 Pipeline Coverage: Growth (%) KA Turner;
J. Ieraci; P. Renders1 250% Jan-23 CREATE 1 Pipeline Coverage: Growth (%) KA Turner;
J. Ieraci; P. Renders1 250% FY23 Q1 CREATE 1 Pipeline Coverage: Growth (%) KA Turner;
J. Ieraci; P. Renders1 250% FY23 Q2 CREATE 1 Pipeline Coverage: Growth (%) KA Turner;
J. Ieraci; P. Renders1 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