Forum Discussion
Help on Visualization
Hi
I have a excel sheet that contains table as below,
| New Vehicle Sales Target | ||||||||||||||||
| Q4 2024 | Q1 2025 | Qualifier 2 previous quarters | 12MR | Qualifier 12MR | ||||||||||||
| Code | Dealer | Location | Presidents Award Groups | ZM | PMA | Actual | Target | % of Target | Actual | Target | % of Target | Actual | Target | % of Target | ||
| XXXXX | Dealer 1 | Place 1 | GROUP 1 | pending - recruiting | Metro | |||||||||||
| XXXXX | Dealer 2 | Place 2 | GROUP 1 | pending - recruiting | rural | |||||||||||
| XXXXX | Dealer 3 | Place 3 | GROUP 1 | pending - recruiting | Metro |
How to bring above table to below visualization format? Please help me on step by step process
| Sales Target | |||
| Actual | Target | % of Target | |
| Q4 2024 | #N/A | #N/A | #N/A |
| Q1 2025 | #N/A | #N/A | #N/A |
| Qualifier 2 previous quarters | #N/A |
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.
5 Replies
Singh_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.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.
Hi Singh_Yoshi,
I wanted to check if you had the opportunity to review the information provided by the community members. Thank you for the respnses. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank you.Hi Singh_Yoshi,
As we have not received a response from you yet, I would like to confirm whether you have successfully resolved the issue or if you require further assistance.
If the issue has been resolved, please mark the helpful reply as a "solution" to indicate that the question has been answered and to assist others in the community.
Thank you for your cooperation. Have a great day.Hi Singh_Yoshi,
May I ask if you have gotten this issue resolved?
If it is solved, please mark the helpful reply or share your solution and accept it as solution, it will be helpful for other members of the community who have similar problems as yours to solve it faster.
Thank you.
