Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
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
Thank you for your reply. I will try soon.
Sur
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
Copying DAX from this post? Click here for a hack to quickly replace it with your own table names
Has this post solved your problem? Please Accept as 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 C
I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com
Hi Allison,
Thanks for your suggestion. Will try to as soon as possible, and will let you know the result.
Rgds,
Sur
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...
Hi Buist,
Thanks for the information. Will learn it asap.
Sur
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
87 | |
64 |