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

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
    • YashLele's avatar
      YashLele
      Regular Visitor
      1 year ago
      Thank you so much ryan_mayu 
      With some tweaking its working perfect now.
      Cheers
      Column =
      VAR _list=CALCULATETABLE(DISTINCT('Weekly Chem Consump'[Date]),ALLEXCEPT('Weekly Chem Consump','Weekly Chem Consump'[date].[Month], 'Weekly Chem Consump'[Plant_Name]))
      return sumx(FILTER('Merged_Data','Weekly Chem Consump'[Plant_Name]='Merged_Data'[City] && 'Merged_Data'[Date] in _list), 'Merged_Data'[Total Units Used])
      • ryan_mayu's avatar
        ryan_mayu
        Super User
        1 year ago

        you are welcome