cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Regular Visitor

How to create column and row hierarchies in tables?

Hi

 

I'm having a bit of trouble creating a matrix out of my dataset

 

I have data in the top format and I want to see it displayed in the bottom format.

 

ID

Type

Status

Received

Reserved

Amount

Commission

 

5

M

Received

02/16/2017

    

4

M

Completed

02/15/2017

02/23/2017

100

20

 

3

B

Received

02/14/2017

    

2

M

Cancelled

01/03/2017

    

1

B

Completed

10/15/2016

01/27/2017

1000

100

 
        
        
  

JAN

FEB

    

M

Count of Received

1

2

    

M

Count of Reserved

0

1

    

M

Sum of Amount

0

100

    

M

Sum of Commission

0

20

    

B

Count of Received

0

1

    

B

Count of Reserved

1

0

    

B

Sum of Amount

1000

0

    

B

Sum of Commission

100

0

    

 

Could anyone help me?  I can only get additional columns added rather than rows and my counts of reserved and received aren't right. 

 

Thanks

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User III
Super User III

Re: How to create column and row hierarchies in tables?

- Unpivot the columns "Received" and "Reserved"

- this creates a column "Value" with the dates: Filter out blanks

- Extract the month from that column

- Group on that column and column "Status" and "Type"

- Create Counts and Sums on "Amount" and "Commission"

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

View solution in original post

4 REPLIES 4
Highlighted
Super User III
Super User III

Re: How to create column and row hierarchies in tables?

- Unpivot the columns "Received" and "Reserved"

- this creates a column "Value" with the dates: Filter out blanks

- Extract the month from that column

- Group on that column and column "Status" and "Type"

- Create Counts and Sums on "Amount" and "Commission"

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

View solution in original post

Highlighted
Regular Visitor

Re: How to create column and row hierarchies in tables?

Thank you.  

 

Didn't know about unpivot and group by functions, but they seem to be what I need.  Still getting some rows where I want columns and a hierarchy that I don't want but think if I fiddle around with it a bit more I should be able to get it to work.

Highlighted
Regular Visitor

Re: How to create column and row hierarchies in tables?

A final question.  I now have, in my datasets, the columns: month, type (M or B), attribute (which has sum of amount, sum of commission, count of received and count of reserved) and value.  This allows me, in my report, to have the columns months and with the rows divided into type and attribute i.e. M + sum of amount, M + sum of commission, M + count of received etc.

 

The problem is the column value contains both counts (whole number) and sums (fixed number).  Is there anyway to display these numbers in different ways in the report? 

Highlighted
Super User III
Super User III

Re: How to create column and row hierarchies in tables?

Having difficulties to follow/image how your table looks like, but maybe you can now pivot on the column that holds Count and Sum. This would produce 2 different columns then.

Otherwise please post picture or sample table - thanks!

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Helpful resources

Announcements
May 2020 Community Highlights

May 2020 Community Highlights

It’s time for another PBI Community recap!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Galleries

Galleries

Looking for inspiration on how to present your data? Need instructional videos? Check out our Galleries!

Top Solution Authors