Forum Discussion
Helper IHelp on Visualization
Hi Singh_Yoshi
Think of the Matrix visual in Power BI as being similar to pivot tables in Excel, rather than simple cell ranges. What you're trying to do isn’t supported out of the box. To implement this, you'll need to create a disconnected table that contains the column values along with their hierarchy, and then reference that table within a measure. This approach requires some additional modeling and isn't entirely straightforward and will take a considerable amount of time to develop. Please refer to the attached sample for more details.
Super UserSingh_Yoshi In the Power Query Editor, you may need to clean and transform your data.
Ensure that the columns are correctly named and formatted.
Remove any unnecessary columns or rows.
In Power BI, go to Modeling > New Table.
Create a new table to summarize the sales target data. You can use DAX (Data Analysis Expressions) to create this table.
DAX
SalesTargetSummary =
UNION(
SELECTCOLUMNS(
SalesData,
"Period", "Q4 2024",
"Actual", SalesData[Actual_Q4_2024],
"Target", SalesData[Target_Q4_2024],
"% of Target", SalesData[Percent_Target_Q4_2024]
),
SELECTCOLUMNS(
SalesData,
"Period", "Q1 2025",
"Actual", SalesData[Actual_Q1_2025],
"Target", SalesData[Target_Q1_2025],
"% of Target", SalesData[Percent_Target_Q1_2025]
),
SELECTCOLUMNS(
SalesData,
"Period", "Qualifier 2 previous quarters",
"Actual", SalesData[Actual_Qualifier_2],
"Target", SalesData[Target_Qualifier_2],
"% of Target", SalesData[Percent_Target_Qualifier_2]
)
)
Go to the Report view in Power BI.
Add a table or matrix visual.
Drag the fields from the SalesTargetSummary table to the visual.
Arrange the fields to match the desired format.