Forum Discussion
Solved
Matrix Row Subtotals Based on Variable Data
Hi all, I'm trying to build out a revenue report based on SKU, Customer, and the number of units each customer ordered using a matrix so I can drill down to the S/N level for each customer. The issue...
Hi,
PBI file attached.
Hope this helps.
Hi Ritaf1983 I've inserted tables for the items I'm talking about below. I've also added the tables seen in the screenshots in my original message. Let me know if there are other questions about these to get to an answer.
Customer Pricing:
| Item Number | Customer Name | Customer Price per Unit |
| 123456 | Acme | 25 |
| 123456 | Bonzo | 20 |
| 123456 | Unity | 30 |
| 234567 | Acme | 15 |
| 234567 | Unity | 20 |
| 234567 | Gargan | 13 |
| 234567 | CMT | 18 |
Items, S/Ns, and Sold-To:
| Item Number | S/N | Customer Sold-To |
| 123456 | 10001 | Acme |
| 123456 | 10002 | Acme |
| 123456 | 10003 | Acme |
| 123456 | 10004 | Acme |
| 123456 | 10005 | Acme |
| 123456 | 10006 | Acme |
| 123456 | 10007 | Bonzo |
| 123456 | 10008 | Bonzo |
| 123456 | 10009 | Bonzo |
| 123456 | 10010 | Bonzo |
| 123456 | 10011 | Bonzo |
| 123456 | 10012 | Bonzo |
| 123456 | 10013 | Bonzo |
| 123456 | 10014 | Bonzo |
| 123456 | 10015 | Bonzo |
| 123456 | 10016 | Unity |
| 123456 | 10017 | Unity |
| 123456 | 10018 | Unity |
| 234567 | 20001 | Acme |
| 234567 | 20002 | Acme |
| 234567 | 20003 | Acme |
| 234567 | 20004 | Acme |
| 234567 | 20005 | Acme |
| 234567 | 20006 | Acme |
| 234567 | 20007 | Acme |
| 234567 | 20008 | Acme |
| 234567 | 20009 | Acme |
| 234567 | 20010 | Unity |
| 234567 | 20011 | Unity |
| 234567 | 20012 | Unity |
| 234567 | 20013 | Unity |
| 234567 | 20014 | Unity |
| 234567 | 20015 | Unity |
| 234567 | 20016 | Unity |
| 234567 | 20017 | Unity |
| 234567 | 20018 | Gargan |
| 234567 | 20019 | Gargan |
| 234567 | 20020 | CMT |
| 234567 | 20021 | CMT |
Correct Output:
| Product Code | Revenue |
| 123456 | $180 |
| Acme | $100 |
| 10001 | $25 |
| 10002 | $25 |
| 10003 | $25 |
| 10004 | $25 |
| Bonzo | $80 |
| 10007 | $20 |
| 10008 | $20 |
| 10009 | $20 |
| 10010 | $20 |
Incorrect Output:
| Product Code | Revenue |
| 123ABC | $45 |
| Acme | $25 |
| 10001 | $25 |
| 10002 | $25 |
| 10003 | $25 |
| 10004 | $25 |
| Bonzo | $20 |
| 10007 | $20 |
| 10008 | $20 |
| 10009 | $20 |
| 10010 | $20 |
Hi,
PBI file attached.
Hope this helps.
Thank you so much for the help Ashish. I have another table that I need to make sure I have all of the Customer Sold-To data populated for each serial number, but this should work once I have that.