cancel
Showing results 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

Anonymous
Not applicable

## Weighted Distribution calculation

Hello,

I have the following table:

 Client Products Sales 100042 product1 24 100070 product2 20 100070 product1 24 100075 product2 20 100075 product3 24 100075 product4 24 100081 product2 20 100081 product3 24

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

 Product1 Value Description Total sales of clients where product1 was sold 68 Total sales of all clients 180 Weighted distribution 37.8% 68/180

The main issue with calculation of [Total sales of clients where product1 was sold]. How to reach it in DAX PBI?

1 ACCEPTED SOLUTION
Solution Sage

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

8 REPLIES 8
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,

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.

Solution Sage

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!

Solution Sage

@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

Frequent Visitor

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.

Solution Sage

Hello @Anonymous

You just need to apply the filter within CALCULATE

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

Proud to be a Datanaut!

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.