Forum Discussion
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 I'm having is that different customers have different pricing for the same SKU and my subtotals in the matrix aren't working correctly.
I have production data that shows each SKU and S/N in one table and I have another table that has pricing for each SKU by customer. Currently the "Revenue" column is set to Sum of Revenue.
Here's what I want to have:
Each customer should be subtotaled based on the unit price of each S/N and then the SKU level should total the Customer subtotals. Unfortunately I'm getting:
Basically it's seeing the correct pricing per customer, but for whatever reason it's not summing the Customer subtotal or SKU totals correctly. Any help would be appreciated.
Hi,
PBI file attached.
Hope this helps.
4 Replies
Hi abrother
Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.
Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...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.
