Weighted distribution column

[Anonymous]

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.

[Anonymous]

@amitchandak @harshnathani @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.

[v-lili6-msft]

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,

Lin

[Anonymous]

@v-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.

[v-lili6-msft]

hi  @Anonymous 

Just create a meausre as below:

All products QTY at Clients where P was sold = 
var _allclients=VALUES('Table 1'[Client ]) return
CALCULATE(SUM('Table 1'[Qty]),FILTER(ALL('Table 1'), 'Table 1'[Client ] in _allclients))

All products QTY at All Clients = CALCULATE(SUM('Table 1'[Qty]),ALL('Table 2'))

Weighted Distribution = DIVIDE([All products QTY at Clients where P was sold],[All products QTY at All Clients])

Result:

and here is sample pbix file, please try it.

Regards,

Lin

[Anonymous]

@v-lili6-msft thanks for your suggestion. It seems to be close, but it doesn't work for my example. I have played around, trying to "adapt" the formula, but I'm missing something. I suppose it is related to the filters that I have (Date and Product)

I've attached xlsx and .pbix files here (https://gofile.io/d/qx4AlR). I have no option to attach the files to thread, sorry.

[v-lili6-msft · Accepted solution]

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

[v-lili6-msft]

hi @Anonymous 

you may need to use ALLxxxx function to in your measure to get it.

https://www.sqlbi.com/articles/managing-all-functions-in-dax-all-allselected-allnoblankrow-allexcept/

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/38490

Regards,

Lin

[amitchandak]

@Anonymous ,Can you share sample data and sample output in table format?

[harshnathani]

Hi @Anonymous ,

Please share sample data and expected output in text format.

Regards,
Harsh Nathani
Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)