Matrix Report- Dynamic column headers

[janani80]

I am pretty new to powerbi and I am required to create a matrix visual based on the table below. Based on slicer selection the header should dynamically change. 

Year	Product	No of Units manufactured	Defective Pieces	Total Sales	Outstanding Output	Discounted items
2022	Handbag	5000	100	4000	800	100
2022	Purse	30000	20	25230	4500	250
2022	Briefcase	2500	30	2350	100	20
2022	Suitcase	6500	45	5955	400	100
2022	Wallet	4310	100	3960	200	50
2023	Handbag	4000	23	2580	1000	397
2023	Purse	20000	120	15000	4000	880
2023	Briefcase	2100	21	1700	100	279
2023	Suitcase	6300	60	5700	400	140
2023	Wallet	4100	22	3000	1000	78
2024	Handbag	34000	225	32635	900	240
2024	Purse	34000	200	32590	1000	210
2024	Briefcase	25000	105	23000	1345	550
2024	Suitcase	12500	205	9970	2200	125
2024	Wallet	45000	650	43000	800	550

If I select a 2023 as the year the data should reflect as below. If I select the year as 2022 the 1st 2 columns should display data for 2021 and the next 3 columns for 2022 and so on. The Year should dynamically change.

	2022	2023
Product	Sales	Outstanding Output	No of Units manufactured	Defective Pieces	Discounted items
Handbag	4000	800	4000	23	397
Purse	25230	4500	20000	120	880
Briefcase	2350	100	2100	21	279
Suitcase	5955	400	6300	60	140
Wallet	3960	200	4100	22	78

Thanks in advance

[v-xinc-msft]

Thank you for the reply from hnguy71  and lbendlin!

Hi @janani80 ,

Did the reply hnguy71 offered help you solve the problem, if it helps, you can consider to accept it as a solution so that more user can refer to, or if you have other problems, you can offer some information so that can provide more suggestion for you.

If the above ones still not help you get the expected result, could you please provide example data or sample files here if you have any confused? We could offer you more help if we have information in detail. And what you expect the output to be. There is sensitive data that can be removed in advance. How to provide sample data in the Power BI Forum - Microsoft Fabric Community

Thanks for your understanding. Your time and cooperation are much valued by us. We are looking forward to hearing from you to assist further.

Best regards,

Lucy Chen

[hnguy71]

Hi @janani80 

While there isn't a straightforward method for it, with a bit of creativity, it's possible.

You'll need at least two disconnected tables similar to this:

The "Disconnected_Yrs" table is just the unique values of your years in your dataset. For simplicity sake, I built it in DAX with the following expression:

Disconnected_Yrs = DISTINCT('FACT'[Year])

The "Metric" table is a table built using the "Enter Data" functionality with the metrics you want to evaluate:

Next, you'll have to create your metric calculation:

Metric_Analysis = 
// What's the current year being evaluated on the matrix visual
VAR _CurrYear = MAX('FACT'[Year])

// What is the selected year by the user?
VAR _SelectedYr = SELECTEDVALUE(Disconnected_Yrs[Year], MAX(Disconnected_Yrs[Year]))

// What is the current metric being evaluated on the matrix visual?
VAR _Metric = SELECTEDVALUE(Metric[Metric])

RETURN

// Determines which year to show which metric.
SWITCH( TRUE(),
    _Metric = "Total Sales" && _CurrYear = _SelectedYr - 1,  CALCULATE(SUM('FACT'[Total Sales]), 'FACT'[Year] = _SelectedYr - 1) ,  
    _Metric = "Outstanding Output" && _CurrYear = _SelectedYr, CALCULATE(SUM('FACT'[Outstanding Output]), 'FACT'[Year] =_SelectedYr),
    _Metric = "Units Manufactured" && _CurrYear = _SelectedYr, CALCULATE(SUM('FACT'[No of Units manufactured]), 'FACT'[Year] = _SelectedYr),
    _Metric = "Defective Pieces" && _CurrYear = _SelectedYr, CALCULATE(SUM('FACT'[Defective Pieces]), 'FACT'[Year] = _SelectedYr),
    _Metric = "Discounted Items" && _CurrYear = _SelectedYr, CALCULATE(SUM('FACT'[Discounted items]), 'FACT'[Year] = _SelectedYr)
)

All that's left is to build a matrix visual. For the simplicity of this excercise, Product and Year are from the same table. Metric is from the disconnected table and the Year slicer is also from the disconnected table. And here's the results:

[janani80]

Hi hnguy71

Thanks for the calculations, however when I tried the same measure for my report(the data is different) I did not get the desired output. Hence I used the same data which I shared with you and the same steps but still I did not get the desired output. I had added the product in the rows, Year in column and the Measure(Metric_Analysis) in the value section. Below is the printscreen for your reference. Am I doing some step wrong?

Thanks

Janani80

[hnguy71]

Hi @janani80 ,
Seems you're also missing the Metric field on the Columns well.

Here's a sample pbix for you to play around: janani80_disconnected_matrix.pbix

[janani80 · Accepted solution]

Thanks hnguy71.. It worked now.. And sorry to disturb you on a weekend.

[lbendlin]

Tell the requestor that is not something you do in Power BI.  Better stay in Excel.