Help on Visualization

[Singh_Yoshi]

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

[v-sgandrathi]

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.

[v-sgandrathi]

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.

[v-sgandrathi]

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.

[danextian · Accepted solution]

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.

[bhanu_gautam]

@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.