cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
raimkulov Frequent Visitor
Frequent Visitor

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.

 

1 ACCEPTED SOLUTION

Accepted Solutions
affan Established Member
Established Member

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

 

 

7 REPLIES 7
LivioLanzo Super Contributor
Super Contributor

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 correctly? Mark my answer as a solution!


Proud to be a Datanaut!  

affan Established Member
Established Member

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

raimkulov Frequent Visitor
Frequent Visitor

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.

affan Established Member
Established Member

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
raimkulov Frequent Visitor
Frequent Visitor

Re: Weighted Distribution calculation

Hi @affan,

 

Thanks a lot!

This is exactly what I needed.

nicolas_walbaum Occasional Visitor
Occasional Visitor

Re: Weighted Distribution calculation

Hi @affan !

 

Could you paste DAX as I cannot open Dropbox files?

 

Thank you!

affan Established Member
Established Member

Re: Weighted Distribution calculation

@nicolas_walbaum 

 

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

Helpful resources

Announcements
Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 306 members 3,067 guests
Please welcome our newest community members: