Forum Discussion

suriyanto_limah's avatar
suriyanto_limah
New Member
6 years ago

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

Need Help

6 Replies

  • AllisonKennedy's avatar
    AllisonKennedy
    Community Champion
    6 years ago

    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's avatar
      suriyanto_limah
      New Member
      6 years ago

      Hi Allison,

       

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

       

      Rgds,

      Sur

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    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