Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
BOBBY21
Regular Visitor

Matrix table couldn't display values

Hi,

I am new to PowerBi and I am not sure if this is an issue or I am doing it wrong. I am developing a matrix report and the column picks either first or last but not all values. In below screen shot, there are orders 5 more unders that have to be displayed for country "US". But it pick's either first or last or count(distinct). Is there other way I can work with this or is it an issue? Capture.PNG

 

 

2 ACCEPTED SOLUTIONS
RamySaroufim
Employee
Employee

Hello Bobby21,

 

The Matrix visual is used to show a measure aggregated over some grouping. The grouping can happen in 2 dimensions, Rows and Columns.

Since you measure is a Text, the options for aggregation function are First, Last and Count.

 

To fix your issue and show exactly as you have on the the spread sheet you have, you need to add another level of grouping that would be unique per row. Something like an Order number.

2017-06-26 17_23_31-Book1 - Excel.png

 

 

 

 

 

Then add the Order # to the Row hierarchy.

 

Ramy

View solution in original post

v-haibl-msft
Employee
Employee

@BOBBY21

 

You can fill down the null countries in Query Editor as RamySaroufim said. And then use the Table visual to show the result as below.

 

Matrix table couldn't display values_1.jpgMatrix table couldn't display values_2.jpg

 

Best Regards,
Herbert

View solution in original post

6 REPLIES 6
v-haibl-msft
Employee
Employee

@BOBBY21

 

You can fill down the null countries in Query Editor as RamySaroufim said. And then use the Table visual to show the result as below.

 

Matrix table couldn't display values_1.jpgMatrix table couldn't display values_2.jpg

 

Best Regards,
Herbert

I tried this and it worked out to an extent, but the business is happy with having 2 other groupings.

 

Thank you.

RamySaroufim
Employee
Employee

Hello Bobby21,

 

The Matrix visual is used to show a measure aggregated over some grouping. The grouping can happen in 2 dimensions, Rows and Columns.

Since you measure is a Text, the options for aggregation function are First, Last and Count.

 

To fix your issue and show exactly as you have on the the spread sheet you have, you need to add another level of grouping that would be unique per row. Something like an Order number.

2017-06-26 17_23_31-Book1 - Excel.png

 

 

 

 

 

Then add the Order # to the Row hierarchy.

 

Ramy

I added 2 other groupings (state and total value of order). This gave out my desired layout. 

BOBBY21
Regular Visitor

I am trying to achieve the below screenshot. On thursday we actually have 3 more orders with in US, simillarly on friday we actually have 2 orders to India and 4 orders with in US. Somehow PowerBI picks either First or last value.  hope the below screenshot makes sense. Numbers in red font color aren't picked in my report.Capture1.PNG

 

 

v-haibl-msft
Employee
Employee

@BOBBY21

 

Could you please post some sample data for your dataset, especially the US country records? And do you mean that there are 5 order number for US, but only 3 order number is displayed in the Matrix in above screenshot?

 

Best Regards,
Herbert

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.