Reply
Frequent Visitor
Posts: 4
Registered: ‎12-20-2018
Accepted Solution

Weighted Distribution calculation

Hello,

 

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

I have the following table:

 

ClientProductsSales
100042product124
100070product220
100070product124
100075product220
100075product324
100075product424
100081product220
100081product324

 

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

 

Product1ValueDescription
Total sales of clients where product1 was sold68 
Total sales of all clients180 
Weighted distribution37.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.

 


Accepted Solutions
Established Member
Posts: 201
Registered: ‎07-12-2017

Re: Weighted Distribution calculation

Hi @raimkulov,

 

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

 

 

View solution in original post


All Replies
Super User
Posts: 751
Registered: ‎09-16-2018

Re: Weighted Distribution calculation

Hello @raimkulov

 

You just need to apply the filter within CALCULATE

 

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



Did I answer your question? Mark my post as a solution!


Proud to be a Datanaut!

Established Member
Posts: 201
Registered: ‎07-12-2017

Re: Weighted Distribution calculation

Hi @raimkulov,

 

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

Frequent Visitor
Posts: 4
Registered: ‎12-20-2018

Re: Weighted Distribution calculation

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.

Established Member
Posts: 201
Registered: ‎07-12-2017

Re: Weighted Distribution calculation

Hi @raimkulov,

 

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

 

 

Highlighted
Frequent Visitor
Posts: 4
Registered: ‎12-20-2018

Re: Weighted Distribution calculation

Hi @affan,

 

Thanks a lot!

This is exactly what I needed.