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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

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
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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