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
Anonymous Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882
Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.
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?