Forum Discussion
Change rows to columns
Anonymous - Take a look at my file link again, second tab. I created 1 measure that is a simple SUM forumula. I then put it in a Matrix, and put the line number in the columns, everything else in rows, and got rid of the subtotals, except for the Transction line, which I thought might be useful, but you can get rid of that too. Then expanded all row descriptions and removed the stepped layout default.
That will automatically expand for every line item, so if a transaction comes in with 6 line items, you'll have 6 columns of data.
You could put the product names in the columns, but you have 13 unique products, so that becomes a very wide table to show all 13, and I suspect your real data has even more actual products.
You could combine this process with the measure MFelix provided if you don't want actual quantities but the concatenation of the product name and total in the Values part. I did a rough version of that on tab 3.
You can pre-expand the entire matrix so your users don't have to, and remove the +/- expansion buttons if desired, also shown on tab 3
Anonymous , the code mhossain provided will work, but this is not a good practice to do. You have a nice normalized table and you are denormalizing it. You will potentially have dozens, hundreds, or even thousands of coluimns over time.
You should instead create a DIM table. You are trying to make your FACT table your DIM table as well.
- Create a reference to your main table
- Select the Transactions column and remove other columns
- Remove duplicates from the transaction column.
- This becomes your Dimension
Let us know what your ultimate goal is - the visual or report output. We can help you get there. De-normalizing your data though will make the DAX very difficult later on and it will not be dynamic as new transaction types come in. Stick to a Star Schema, which has a normalized FACT table.
Hi edhans
I thought product columns will be fixed to 4, as per Anonymous requirement that max only 4 transaction ID for any records, maybe I missed it, please check.
That code was not cleaned one just for reference and keeping in mind above and the data/report output.
I agree with you on the data strucutring side.