Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

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

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

 

 

View solution in original post

8 REPLIES 8
affan
Solution Sage
Solution Sage

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
Not applicable

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.

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
Not applicable

Hi @affan !

 

Could you paste DAX as I cannot open Dropbox files?

 

Thank you!

@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

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
Not applicable

Hi @affan,

 

Thanks a lot!

This is exactly what I needed.

LivioLanzo
Solution Sage
Solution Sage

Hello @Anonymous

 

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!  

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors