Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Weighted Distribution calculation

Hello,   Could you please help me to calculate weighted distribution using DAX? I have the following table:   Client Products Sales 100042 product1 24 100070 product2 20 100070...
Need Help
  • affan's avatar
    affan
    7 years ago

    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

     

     

affan's avatar
affan
Solution Sage
7 years ago

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's avatar
Anonymous
Not applicable
7 years ago

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's avatar
    affan
    Solution Sage
    7 years ago

    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's avatar
      Anonymous
      Not applicable
      7 years ago

      Hi affan,

       

      Thanks a lot!

      This is exactly what I needed.

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Hi affan !

       

      Could you paste DAX as I cannot open Dropbox files?

       

      Thank you!

      • affan's avatar
        affan
        Solution Sage
        7 years ago

        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