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. 

[Greg_Deckler]

@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.

[Anonymous]

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. 

[v-yueyunzh-msft]

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

[Anonymous]

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

[v-yueyunzh-msft]

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

[Anonymous]

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. Renders	1	RED	250%
Aug-22	CREATE	1	Pipeline Coverage: Growth (%)	KA Turner; J. Ieraci; P. Renders	1	GREEN	250%
Sep-22	CREATE	1	Pipeline Coverage: Growth (%)	KA Turner; J. Ieraci; P. Renders	1		250%
Oct-22	CREATE	1	Pipeline Coverage: Growth (%)	KA Turner; J. Ieraci; P. Renders	1	AMBER	250%
Nov-22	CREATE	1	Pipeline Coverage: Growth (%)	KA Turner; J. Ieraci; P. Renders	1	NONE	250%
Dec-22	CREATE	1	Pipeline Coverage: Growth (%)	KA Turner; J. Ieraci; P. Renders	1		250%
Jan-23	CREATE	1	Pipeline Coverage: Growth (%)	KA Turner; J. Ieraci; P. Renders	1		250%
FY23 Q1	CREATE	1	Pipeline Coverage: Growth (%)	KA Turner; J. Ieraci; P. Renders	1		250%
FY23 Q2	CREATE	1	Pipeline Coverage: Growth (%)	KA Turner; J. Ieraci; P. Renders	1		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

[v-yueyunzh-msft]

Hi , @Anonymous 

According to your description, i do not understand the " Oct to Dec (three  columns) for actuals that is in text format".

Do you mean when i select the "Q1(FY23 Q1 )", it will show this value:

I don't know if I understand correctly? Whether to select Q1, calculate [Date]="FY23 Q1" || [Date]="Oct-22" || [Date] = "Nov-22" || [Date]="Dec-22"?

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]

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. 

[v-yueyunzh-msft]

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]

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

[v-yueyunzh-msft]

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

[Anonymous]

Sorry it should be Oct Nov December. My bad. 

[Anonymous]

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

[v-yueyunzh-msft]

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

[Anonymous]

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]       )))))

[v-yueyunzh-msft]

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

[Anonymous]

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

[v-yueyunzh-msft]

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

[Anonymous]

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? 

[Anonymous]

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.