Display all data points in table or matrix

[hemann]

Hi PBI Gurus, I want to display all data points for products by orientation and not have it summarise the data. Below is a sample of the data table. Also below is the desired result with the orientation across the top. I have tried pivoting the table however my data is too large. Any suggestions? I thought a virtual table may work but not sure how to achieve it. thanks. 

Data Table sample. 

Desired result in a table visual. 

[v-fenling-msft]

Hi, @hemann 

May I ask if you have gotten this issue resolved? If it is solved, please 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.

If it is not resolved, I hope you will provide the full .pbix file via OneDrive or SharePoint. Please be careful to remove all sensitive information and we will do our best to provide ideas for your issue.

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Fen Ling,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Ashish_Mathur]

Hi,

In the Query Editor, we will have to assign an Index number first and then build a matrix visual.

[v-fenling-msft]

Thanks for AllisonKennedy's concern about this issue.

Hi, @hemann 

I am glad to help you.

First you can open Transform data and Ctrl+click to select the Qrientation column and Value column for pivot column:

Then Ctrl+click select columns 1,2,3,4 and use replace value to change null to 0:

Result:

I have attached the pbix file below, hope this helps!

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Fen Ling,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[hemann]

I tried this my real data is too large for this to work. 

[AllisonKennedy]

@hemann just to be clear, you do not need to pivot your data - this can be done in the matrix visual. As @Ashish_Mathur  and I have said, all you need to do is add an index. Then you'll need to mark the product as the row identifier. Please let us know if you're able to solve it.

[AllisonKennedy]

@hemann  Power BI visuals will always aggregate / summarize the values. You can get around this by adding a primary key to your table. So from your raw data table sample, in Power Query add an Index column.

Then load to Power BI.

In the model (relationships) view for Power BI, select the table the 'Product' column is in and set 'Product' as the row lable for the column, then set the 'Index' column as the key: 

This will allow your products to show up without aggregation, and you'll get a new row for each line in your table: 

[hemann]

This does not work as I don't get the orientation at column level in the visual. Refer desired result. 

[v-fenling-msft]

Hi, @hemann 

May I ask if you have solved the problem you are experiencing?

May I ask if you have tried AllisonKennedy  and Ashish_Mathur  to see if their methods work? In my testing, the methods they offer work, but I don't have so much data for me to test:

New index column:

I have attached the pbix file for this small example below, I hope it helps.

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Fen Ling,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.