Weighted Distribution calculation

[Anonymous]

Hello,

Could you please help me to calculate weighted distribution using DAX?

I have the following table:

Client	Products	Sales
100042	product1	24
100070	product2	20
100070	product1	24
100075	product2	20
100075	product3	24
100075	product4	24
100081	product2	20
100081	product3	24

I need to calculate weighted distribution using DAX formula for each product, example of calculation for product1 is below:

Product1	Value	Description
Total sales of clients where product1 was sold	68
Total sales of all clients	180
Weighted distribution	37.8%	68/180

The main issue with calculation of [Total sales of clients where product1 was sold]. How to reach it in DAX PBI?

Thanks in advance.

[affan]

Hi @Anonymous,

You can used the below dax measure.

WeightedAvg = var _total=CALCULATE(SUM(Table1[Sales]),ALL(Table1))
var _prodsum=CALCULATE(SUM(Table1[Sales]))
Return
    _prodsum/_total

You can find the pbix file here.

If this helped you, please mark this post as an accepted solution and like to give KUDOS .

Regards,

Affan

[Anonymous]

Hi @affan,

Thanks for the reply!

Unfortunately, this is not what I am asking for.

In your pbi-file for product1 weighted distribution is 26.67% but it should be 37.78%, numerator should be 68 not 48 (24+24+20).

I need to sum total sales of clients where product1 was sold. It means that I sum not only product1 but produt2 for client 100070.

[affan · Accepted solution]

Hi @Anonymous,

Please see the attached PBIX file update with your requirement. I have created a calculated column to calculate your desired result.

If this helped you, please mark this post as an accepted solution and like to give KUDOS .

Regards,

Affan

[Anonymous]

Hi @affan !

Could you paste DAX as I cannot open Dropbox files?

Thank you!

[affan]

@Anonymous 

I have created a custom column 

CustSum = CALCULATE(SUM(Table1[Sales]),FILTER(ALL(Table1),Table1[Client]=EARLIER(Table1[Client])))

Then I have created the following measure

WeightedAvg = var _total=CALCULATE(SUM(Table1[Sales]),ALL(Table1))
var _prodsum=CALCULATE(SUM(Table1[CustSum]))

Return
  _prodsum/_total

Regards

Affan

[Stoppelaar]

Hi @affan 

Many thanks for this! 

it works fine if I only look at one day but I have years with data and I want to to show the % if I change from one day to 3 years (or what ever date/week/month/year)?

Many thanks, 

Andreas

[Anonymous]

Hi @affan,

Thanks a lot!

This is exactly what I needed.

[LivioLanzo]

Hello @Anonymous

You just need to apply the filter within CALCULATE

CALCULATE( <your sum measure>, Products[Product] = 'Product1' )