Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Weighted distribution column

Hi,   I need to calculate Sum of Sales for all Clients where a specific product was sold. EX.:   I have a matrix table, with the products as Rows and I need the Total sales, Sum of Sales for all ...
Need Help
Show and Tell
  • v-lili6-msft's avatar
    v-lili6-msft
    6 years ago

    hi  Anonymous 

    Just adjust the formula as below:

     

    All products QTY at Clients where P was sold 2 = 
var _allclients=VALUES('Sales Table'[Client ]) 
Return
CALCULATE(SUM('Sales Table'[Qty]),FILTER(ALLEXCEPT('Sales Table','Sales Table'[Date]),'Sales Table'[Client ] in _allclients))

     

     

     

    Regards,

    Lin

Anonymous's avatar
Anonymous
Not applicable
6 years ago

amitchandak Anonymous v-lili6-msft 

Sales data table format:

DateClient Product Qty
8/21/2020C1P30.1
8/22/2020C2P10.3
8/23/2020C3P20.89
8/24/2020C4P30.94
8/25/2020C5P10.4
8/26/2020C6P20.1
8/27/2020C7P10.6
8/28/2020C8P30.7

 

Brand Name table format: 

 

ProductNaming Convention
P1Product 1
P2Product 2
P3Product 3

 

Below you can see the expected results:

 ClientsP QtyAll products QTY at All ClientsAll products QTY at Clients where P was soldWeighted Distribution
P128372.663.7755.8288%
P228932.3763.7755.0686%
P318201.2463.7746.2473%

 

I already have the columns "Clients, P Qty, All products QTY at All Clients." Now I just need the "All products QTY at Clients where P was sold " and once I have it, I can calculate the Weighted Distribution by dividing: "All products QTY at Clients where P was sold "/ "All products QTY at All Clients" = "Weighted Distribution"

Thanks in advance.

  • v-lili6-msft's avatar
    v-lili6-msft
    Community Support
    6 years ago

    hi  Anonymous 

    Based your sample data, we could not get how to calculate [All products QTY at Clients where P was sold]

     

    and i think if you just use this formula:

    All products QTY at Clients where P was sold = CALCULATE(SUM(Sales[Qty]))
     
    please share more sample data for this.
     
    Regards,
    Lin
    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      v-lili6-msft Thanks for your reply.

      Table 1:

      Client Product Qty
      C1P30.1
      C2P10.3
      C3P20.89
      C4P30.94
      C1P10.4
      C2P20.1
      C3P10.6
      C4P3

      0.7

       

       

       

      Table 2:

      ProductNaming Convention
      P1Product 1
      P2Product 2
      P3Product 3

       

       

      The outputs, based on datasets are:

       ClientsP QtyAll products QTY at All ClientsAll products QTY at Clients where P was soldWeighted Distribution
      P131.34.032.3959%
      P220.994.031.8947%
      P321.744.032.1453%

       

      Appreciate your help.

      • v-lili6-msft's avatar
        v-lili6-msft
        Community Support
        6 years ago

        hi  Anonymous 

        Just create a meausre as below:

        All products QTY at Clients where P was sold = 
var _allclients=VALUES('Table 1'[Client ]) return
CALCULATE(SUM('Table 1'[Qty]),FILTER(ALL('Table 1'), 'Table 1'[Client ] in _allclients))

         

        All products QTY at All Clients = CALCULATE(SUM('Table 1'[Qty]),ALL('Table 2'))
        Weighted Distribution = DIVIDE([All products QTY at Clients where P was sold],[All products QTY at All Clients])

        Result:

         

        and here is sample pbix file, please try it.

         

        Regards,

        Lin