Multi column Matrix or Table

[suriyanto_limah]

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:

UnitID	Sold in Period	Cancel in Period	Cancel out Period	Re-open in Period	Re-open out Period	SalesAmount
AA-01	1	0	0	0	0	$100
AA-02	1	0	0	0	0	$100
AA-03	1	0	0	0	0	$100
AA-04	1	0	0	0	0	$100
AA-01	0	-1	0	0	0	$100
AA-02	0	0	-1	0	0	$100
AA-01	0	0	0	0	1	$100

Expected to produce report like this: 

Description	Unit	Amount
Sold in Period	4	$400
Cancel in Period	-1	-$100
Re-Open in Period	0	0
**Sub Total in Period	3	$300
Cancel out Period	-1	-$100
Re-open out Period	1	$100
**Net Sales	3	$300

Thank you. 

Rgds,

Sur 

[amitchandak]

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

[suriyanto_limah]

Thank you for your reply. I will try soon.  

Sur

[AllisonKennedy]

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

[suriyanto_limah]

Hi Allison,

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

Rgds,

Sur

[Anonymous]

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... 

[suriyanto_limah]

Hi Buist, 

Thanks for the information. Will learn it asap.

Sur