Forum Discussion
Create Dynamic Matrix Table
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 |
I found solution to my problem following Stachu directions in below link.
Solved: Create calculated row - Microsoft Power BI Community
5 Replies
I found solution to my problem following Stachu directions in below link.
Solved: Create calculated row - Microsoft Power BI Community
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.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 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
