Forum Discussion
How to put second row step total as a column in matrix
Hello, I have this table below that shows every month, the total counts, weight and monthly award target per lane and then by each id one step below it (the crossed out blue stuff).
My question is, how can I create a new measure as a matrix value that shows me each ids weight divided by that lanes total weight. IE number 3 would be 7/3009. I want to essentially get the percentage for each one. I tried to do show as % of totals but it didnt seem to work, rather it did it for every month and lane. I think its because im on the second row level.
Please help
4 Replies
- audreygerred
Super User
Hello! I created something similar with demo data.
In rows I have month, category and class. For each month we see a total of quantity by category (this would be the equivalent of your line), then when you are in the level of class (equivalent to your ID) it shows what percentage of the category it makes up in that month.
Here are the measures I made to achieve this:
Order Quantity = SUM(Sales[Order Quantity])Monthly Total Order Quantity =CALCULATE([Order Quantity],ALLEXCEPT('Date', 'Date'[Month] ), ALLEXCEPT('Product','Product'[Category]))and last, but not least....Percent of Monthly Total =DIVIDE([Order Quantity],[Monthly Total Order Quantity],0)Very close but it appears we are one step under the solution, ie the lane's cut is being calculated under the month and then the ids under that. How can I drop it one down?
- audreygerred
Hmm - I purposely put in a month, and then two more dimensions so that it would mimic what you had with month, line and id (I just did month, category and class).
