Forum Discussion

YashLele's avatar
YashLele
Regular Visitor
1 year ago
Solved

Summing units

 table 1 city,  nickel quantity, date Bangalore  433.00 09/06/24 Bangalore  325.00 16/06/24 Bangalore  220.00 23/06/24 Bangalore  320.00 30/06/24 Hosur  406.00        09/06/24 Hosur  193.00   ...
Need Help
  • ryan_mayu's avatar
    ryan_mayu
    1 year ago

    YashLele  

    you can try this

     

    Column =
    VAR _list=CALCULATETABLE(DISTINCT('Table'[Date]),ALLEXCEPT('Table','Table'[Plant_Name]))
    return sumx(FILTER('Table (2)','Table'[Plant_Name]='Table (2)'[City] && 'Table (2)'[Date] in _list), 'Table (2)'[Total Units Used])
     
     
    pls see the attachment below
ryan_mayu's avatar
ryan_mayu
Super User
1 year ago

what's the expected output based on the sample data you provided?

YashLele's avatar
YashLele
Regular Visitor
1 year ago

it should display the sum of total units used for each date of the month for each city in a column in table 1.

  • ryan_mayu's avatar
    ryan_mayu
    Super User
    1 year ago

    you can try this

     

    Column = sumx(FILTER('Table (2)','Table (2)'[city]='Table'[city] && year('Table (2)'[date])=year('Table'[date])&&month('Table (2)'[date])=month('Table'[date])),'Table (2)'[units])
     
    pls see the attachment below
     
    • YashLele's avatar
      YashLele
      Regular Visitor
      1 year ago

      divyed  ryan_mayu Angith_Nair 

      Sorry if the sample data was not coherent.

      I have given the data below.
      date format is mm/dd/yyyy
      Table 2 consists of all the dates in the month.
      So i want to check the dates from table 1 and then sum the total units from table 2 corresponding to the date.

      Table 1

      Plant_NameWeek No.QuantityDate
      Bangalore2433.006/9/2024
      Bangalore3325.006/16/2024
      Bangalore4220.006/23/2024
      Bangalore5320.006/30/2024
      Hosur2406.006/9/2024
      Hosur3193.006/15/2024
      Hosur4278.006/23/2024

       

       

      Table 2

      CityDateTotal Units Used
      Bangalore6/1/20249511
      Bangalore6/2/20247628
      Bangalore6/3/202411124
      Bangalore6/4/202411607
      Bangalore6/5/202411321
      Bangalore6/6/202411519
      Bangalore6/7/202411770
      Bangalore6/8/202411537
      Bangalore6/9/20246452
      Bangalore6/10/202411184
      Bangalore6/11/202411502
      Bangalore6/12/202411472
      Bangalore6/13/202411930
      Bangalore6/14/202411924
      Bangalore6/15/202411478
      Bangalore6/16/20243445
      Bangalore6/17/202410465
      Bangalore6/18/202411255
      Bangalore6/19/202411317
      Bangalore6/20/202411752
      Bangalore6/21/202411936
      Bangalore6/22/202411139
      Bangalore6/23/20242224
      Bangalore6/24/202410548
      Bangalore6/25/202411663
      Bangalore6/26/202412685
      Bangalore6/27/20249941
      Bangalore6/28/202410578
      Bangalore6/29/202411184
      Bangalore6/30/20246952
      Hosur6/1/20245472
      Hosur6/2/202410114
      Hosur6/3/202410904
      Hosur6/4/202411076
      Hosur6/5/202410744
      Hosur6/6/202410678
      Hosur6/7/202410848
      Hosur6/8/202410578
      Hosur6/9/20248162
      Hosur6/10/202410350
      Hosur6/11/202410734
      Hosur6/12/202411358
      Hosur6/13/202411220
      Hosur6/14/202410962
      Hosur6/15/20246652
      Hosur6/16/202410992
      Hosur6/17/202411198
      Hosur6/18/202411064
      Hosur6/19/202411166
      Hosur6/20/20249800
      Hosur6/21/202411352
      Hosur6/22/20249000
      Hosur6/23/20242280
      Hosur6/24/20248790
      Hosur6/25/202411430
      Hosur6/26/202411484
      Hosur6/27/202411682
      Hosur6/28/202411424
      Hosur6/29/202411222
      Hosur6/30/20242496
      • ryan_mayu's avatar
        ryan_mayu
        Super User
        1 year ago

        YashLele  

        you can try this

         

        Column =
        VAR _list=CALCULATETABLE(DISTINCT('Table'[Date]),ALLEXCEPT('Table','Table'[Plant_Name]))
        return sumx(FILTER('Table (2)','Table'[Plant_Name]='Table (2)'[City] && 'Table (2)'[Date] in _list), 'Table (2)'[Total Units Used])
         
         
        pls see the attachment below