Forum Discussion
Weighted distribution column
Hi,
I need to calculate Sum of Sales for all Clients where a specific product was sold. EX.:
I have a matrix table, with the products as Rows and I need the Total sales, Sum of Sales for all Clients where a specific product was sold, and Weighted Distribution % as Columns.
I have calculated the Total sales but now I need the other variable "Sum of Sales for all Clients where a specific product was sold" to be able to calculate the Weighted Coverage.
Data Model consist of 2 tables:
1. Sales data - with Date, Client Name, Brand Name, Quantity
2. Brand Name - with Brand Name, Naming Convention
Key field of the relationship is Brand Name.
As a filter I have the date and the brand name.
Thanks.
hi Anonymous
Just adjust the formula as below:
All products QTY at Clients where P was sold 2 = var _allclients=VALUES('Sales Table'[Client ]) Return CALCULATE(SUM('Sales Table'[Qty]),FILTER(ALLEXCEPT('Sales Table','Sales Table'[Date]),'Sales Table'[Client ] in _allclients))Regards,
Lin
9 Replies
Anonymous ,Can you share sample data and sample output in table format?
hi Anonymous
you may need to use ALLxxxx function to in your measure to get it.
if not your case, please share your sample pbix file and expected output, that will be a great help.
Please see this post regarding How to Get Your Question Answered Quickly:
https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490Regards,
Lin
amitchandak Anonymous v-lili6-msft
Sales data table format:
Date Client Product Qty 8/21/2020 C1 P3 0.1 8/22/2020 C2 P1 0.3 8/23/2020 C3 P2 0.89 8/24/2020 C4 P3 0.94 8/25/2020 C5 P1 0.4 8/26/2020 C6 P2 0.1 8/27/2020 C7 P1 0.6 8/28/2020 C8 P3 0.7 Brand Name table format:
Product Naming Convention P1 Product 1 P2 Product 2 P3 Product 3 Below you can see the expected results:
Clients P Qty All products QTY at All Clients All products QTY at Clients where P was sold Weighted Distribution P1 2837 2.6 63.77 55.82 88% P2 2893 2.37 63.77 55.06 86% P3 1820 1.24 63.77 46.24 73% I already have the columns "Clients, P Qty, All products QTY at All Clients." Now I just need the "All products QTY at Clients where P was sold " and once I have it, I can calculate the Weighted Distribution by dividing: "All products QTY at Clients where P was sold "/ "All products QTY at All Clients" = "Weighted Distribution"
Thanks in advance.
hi Anonymous
Based your sample data, we could not get how to calculate [All products QTY at Clients where P was sold]
and i think if you just use this formula:
All products QTY at Clients where P was sold = CALCULATE(SUM(Sales[Qty]))please share more sample data for this.Regards,Linv-lili6-msft Thanks for your reply.
Table 1:
Client Product Qty C1 P3 0.1 C2 P1 0.3 C3 P2 0.89 C4 P3 0.94 C1 P1 0.4 C2 P2 0.1 C3 P1 0.6 C4 P3 0.7
Table 2:
Product Naming Convention P1 Product 1 P2 Product 2 P3 Product 3 The outputs, based on datasets are:
Clients P Qty All products QTY at All Clients All products QTY at Clients where P was sold Weighted Distribution P1 3 1.3 4.03 2.39 59% P2 2 0.99 4.03 1.89 47% P3 2 1.74 4.03 2.14 53% Appreciate your help.
