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
Highlighted
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

 

 

View solution in original post

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.

Highlighted
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

 

 

View solution in original post

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
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

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

Users Online
Currently online: 113 members 1,606 guests
Please welcome our newest community members: