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
Many thanks edhans for the reply -
My ultimate goal is to create a visual that displays a product pick-up schedule.
The visual needs to be a table with the following columns (left to right) - I omitted some of these columns in my original post for the sake of simplicity but want to make sure I am able to display multiple data from each transaction.
Column 1 : Date and load start time
Column 2: Product load end date and time
Column 3: Transaction
Column 4: Customer
Column 5: Physical destination of product
Column 6: Product 1
Column 7: Product 2
Column 8: Product 3
Column 9: Product 4
Column 10: Special instructions?notes
Appreciate your support,
Mohs
Hi Anonymous ,
You can create measures that will filter your data by the product type. For example:
Product A =
VAR varProductName = "A"
VAR Result =
CALCULATE(
SUM('Table'[Qty]),
'Table'[Product] = varProductName
)
RETURN
Result
Then simply drop this 'Product A' measure into a column on your table. Repeat for B, C, D, etc.
You didn't have a lot of the columns you wanted in your sampled data (customer, special instructions, etc.) but those would likely just shake out to be on the correct row for a given measure. I have created the Product A and B measures and it returns this:
But your data is still fully normalized. In fact, I didn't do any transformations to your original data.
Attached is my PBIX file if you want to take a look. Note that I am using a date table, and the date in the table visual is from that, not your data table. This will allow time intelligence if you want - vs last month, prior year, whatever.
If you need more help, please provide a representative set of data in a usable (not image) format, and exactly what you want the output to look like, but hopefully this will get you started.
How to get good help fast. Help us help you.
How to Get Your Question Answered Quickly
How to provide sample data in the Power BI Forum