cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
New Member

Multi column Matrix or Table

Hi all friends. I have read some discussion posted in this forum and found it is very useful and positive. I've just join the community. I am new in Power BI. I would like to ask the case below. I have no idea to complete the report although had spent much time to get the answer. Hope get solution here. I have a Transacion table like this:

UnitIDSold in PeriodCancel in PeriodCancel out PeriodRe-open in PeriodRe-open out PeriodSalesAmount
AA-011 0 00 0 $100
AA-021 0 00 0$100
AA-031 0 00 0$100
AA-041 0 00 0$100
AA-010 -10 0$100
AA-020 0 -10 0$100
AA-010 0 00 1$100

 

Expected to produce report like this: 

DescriptionUnitAmount
Sold in Period4$400
Cancel in Period -1-$100
Re-Open in Period00
**Sub Total in Period3$300
Cancel out Period-1-$100
Re-open out Period1$100
**Net Sales3$300

Thank you. 

 

Rgds,

Sur 

6 REPLIES 6
Highlighted
Helper I
Helper I

Re: Multi column Matrix or Table

Within the power query (transform data) use the un-pivot option to alter your data table. Heres a link with lots of info:https://support.microsoft.com/en-us/office/unpivot-columns-power-query-0f7bad4b-9ea1-49c1-9d95-f5882... 

Highlighted
Super User IV
Super User IV

Re: Multi column Matrix or Table

Yes, you can do this in Power Query Editor unpivot.

 

Click Transform Data in home tab to open Power Query Editor.

Select UnitID and Sales Amount columns. In the Transform tab click Unpivot other columns.

You'll now have an 'Attribute' and 'Values' column. Rename these 'Description' and 'Unit'

 

This will only work if SalesAmount column is unit price. If it is total you will need to be a bit careful with how you use SalesAmount.

 

Then in Home tab, click Close and Apply. Then just add Description, Unit and Sales amount to a table or matrix visualization.

 

Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved. 

 

If you found this post helpful, please give Kudos.

I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query. 

https://sites.google.com/site/allisonkennedycv

 


______________


Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved. 


If you found this post helpful, please give Kudos.


I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query. 


excelwithallison.com

Highlighted
Super User IX
Super User IX

Re: Multi column Matrix or Table

There is an option show on row, that should have worked if you only plan to show measure on row.

https://www.burningsuit.co.uk/blog/2019/04/7-secrets-of-the-matrix-visual/

 

But for this one you might have to create a table

like

union
(
summarize(Table, table[UnitID], "Type", "Unit", "Sold in Period", "Sold in Period", sum(Table[Sold in Period])), // Add other columns in same manner
summarize(Table, table[UnitID], "Type", "SalesAmount", "Sold in Period", "Sold in Period", sumx(filter(Table,Table[Sold in Period]=1),table[SalesAmount])) // Add other columns in same manner
)

 

use show on row



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Highlighted
New Member

Re: Multi column Matrix or Table

Hi Buist, 

 

Thanks for the information. Will learn it asap.

 

Sur

Highlighted
New Member

Re: Multi column Matrix or Table

Hi Allison,

 

Thanks for your suggestion. Will try to as soon as possible, and will let you know the result.

 

Rgds,

Sur

Highlighted
New Member

Re: Multi column Matrix or Table

Thank you for your reply. I will try soon.  

 

Sur

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Find your favorite faces from the community presenting at the Power Platform Community Conference!

Upcoming Events

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors