cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
UsePowerBI
Post Prodigy
Post Prodigy

Pivot Formatting

Hello

 

I have this table:

ProductPropertyMinMax
ID1A13
ID1B24
ID1C36
ID2A45
ID2B12
ID2C24

 

And I need to create this pivot:

PropertyMinMaxDistinct Count of Product
A131
A451
B121
B241
C241
C361

 

Any idea how to format the above?

 

Thanks!

1 ACCEPTED SOLUTION
v-jayw-msft
Community Support
Community Support

Hi @UsePowerBI ,

 

In Power BI matrix visual, you will need to put both [Product] and [Property] in Rows field.

And the result would be like below. Otherwise the values will be aggregated.

vjaywmsft_0-1658997313880.png

 

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.

View solution in original post

7 REPLIES 7
v-jayw-msft
Community Support
Community Support

Hi @UsePowerBI ,

 

In Power BI matrix visual, you will need to put both [Product] and [Property] in Rows field.

And the result would be like below. Otherwise the values will be aggregated.

vjaywmsft_0-1658997313880.png

 

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.

Changing the layout form of a PivotTable


To make substantial layout changes to a PivotTable or its various fields, you can use one of three forms:

Compact form displays items from different row area fields in one column and uses indentation to distinguish between the items from different fields. Row labels take up less space in compact form, which leaves more room for numeric data. Expand and Collapse buttons are displayed so that you can display or hide details in compact form. Compact form is saves space and makes the PivotTable more readable and is therefore specified as the default layout form for PivotTables.

PivotTable in compact form

Tabular form displays one column per field and provides space for field headers.

PivotTable in tabular form

Outline form is similar to tabular form but it can display subtotals at the top of every group because items in the next column are displayed one row below the current item.

 

Regards,

Rachel Gomez

UsePowerBI
Post Prodigy
Post Prodigy

@Ashish_Mathur thanks, this comes directly from Excel, hope the below helps?

Source:

UsePowerBI_1-1658735733438.png

 

Pivot:

UsePowerBI_0-1658735693583.png

 

Result:

UsePowerBI_2-1658735762949.png

 

Hi,

Drag Property, Min and Max to a Table visual and write this measure

Measure = distinctcount(Data[Property])

Hope this helps.

 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

@Ashish_Mathur  thanks but I need this in a matrix/pivot visual, sorry if it wasn't clear

No where in your previous posts did you mention that you wanted the result in a card visual.  Anyways, now i am confused about what you want.  Show the exact result you want.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Ashish_Mathur
Super User
Super User

Hi,

Property A has 2 distinctcount products - ID1 and ID2.  So the answer should be 2.  This is same for the other properties as well.  How do you get the answer as 1 in each row?


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
November 2022 Update

Check it Out!

Click here to read more about the November 2022 updates!

Microsoft 365 Conference â__ December 6-8, 2022

Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Power BI Dev Camp Session 27

Ted's Dev Camp

This session walks through creating a new Azure AD B2C tenant and configuring it with user flows and custom policies.