Create Dynamic Matrix Table

[dokat]

Hi,

I have a matrix table like below in my power bi dashboard. I created the table by bringing P&L lines and values from Field Pane. The table is static it only pulls YTD 2022 vs 2021. However i have 3 time periods in my report, last year, latest 4 weeks and year to date. Tricky part is all the 4 out of 5  P&L lines are coming from the data table however "Spend" is calculation and not part of original data table. and i need rows to be in the same order below. How can make this table dynamic so that when i change the slicer data gets updated  with selected period ? Appreciate anyones help.!

	2021	2022
Sales	$100	$110
COGS	$50	$55
Spend	$10	$11
Profit	$40	$44
Contrbution	$30	$33

[dokat · Accepted solution]

I found solution to my problem following @Stachu  directions in below link. 

Solved: Create calculated row - Microsoft Power BI Community

[Greg_Deckler]

@dokat If I am following this correctly, many people solve this by using a disconnected table in a slicer and a measure that returns the desired measure based upon what is chosen in the slicer. So, use SELECTEDVALUE to grab the value of the slicer and then a SWITCH statement to return the correct measure.

Otherwise, 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.

[dokat]

@Greg_Deckler Thank you for your response. The data is in below format, only difference is row "spend" is not included and it is a calculated measure. Everything else is coming from P&L table. I would like create a dynamic matrix table like below and updated it based on slicer period selection. Hope this helps clarify. My report has confidential information because of that difficult to share. Thanks

Slicer Option: YTD, Last 4 weeks, and Last Year. 

	2021	2022
Sales	$100	$110
COGS	$50	$55
Spend	$10	$11
Profit	$40	$44
Contrbution	$30	$33

[Greg_Deckler]

@dokat Really need mock data that emulates your real data. And your measure formula. So is your source data like:

Category	Date	Amount
Sales	1/1/2022	$10
Sales	1/2/2022	$20
Spend	1/1/2022	$5
Spend	1/2/2022	$5

[dokat]

@Greg_Deckler below you will find the link to the sample data file. I included year ende 2021 and 2020 and latest 4 weeks data.

Calculation for "Spend" is Sales - Trade. All other PL lines comes from the data table. Thanks

https://www.dropbox.com/s/87dz0f9ibx30l3h/Sample%20Data.pbix?dl=0

	2021	2022
Sales
COGS
Spend
Profit
Contrbution