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
divyed's avatar
divyed
Super User
1 year ago

Hello YashLele ,

 

I  am pasting dax for 2 measures to give totals, please use it as needed.

 

Total_Units_By_City1 =
CALCULATE(
    SUM(Table2[Total_Unit]),
    FILTER(
        Table2,
        Table2[city] = SELECTEDVALUE(Table1[city])
    ),
    REMOVEFILTERS(Table2[date])
)
 
 
Total_Units_Measure =
CALCULATE(
    SUM(Table2[Total_Unit]),
    FILTER(
        Table2,
        Table2[city] = SELECTEDVALUE(Table1[city]) &&
        Table2[date] = SELECTEDVALUE(Table1[date])
    )
)

 

 

 

I hope this is what you are looking for . Please mark this as solution if this helps. Kudos are appreciated.

 

Cheers.

 

YashLele's avatar
YashLele
Regular Visitor
1 year ago

 Hellodivyed,

Both of them are giving me blank output.

  • divyed's avatar
    divyed
    Super User
    1 year ago

    Hello YashLele ,

     

    Please update table and field  names , It should work as I have tested on dummy data.The visual shown is a table and I have created a measure  Total_Units_Measure  and added in the table . Let me know if this works.

     

    Cheers

    • YashLele's avatar
      YashLele
      Regular Visitor
      1 year ago

      divyed ryan_mayu Angith_Nair 

      Sorry if the data was not clear

      i have given the data again.

      There are all the dates in the month in table 2.
      i want to check the dates from table 1 and sum the units used corresponding to those dates only for each month and each city.

      Table 1

      Plant_Name Week No. Quantity Date
      Bangalore 2 433.00 6/9/2024
      Bangalore 3 325.00 6/16/2024
      Bangalore 4 220.00 6/23/2024
      Bangalore 5 320.00 6/30/2024
      Hosur 2 406.00 6/9/2024
      Hosur 3 193.00 6/15/2024
      Hosur 4 278.00 6/23/2024

       

      Table 2 

      City Date Total Units Used
      Bangalore 6/1/2024 9511
      Bangalore 6/2/2024 7628
      Bangalore 6/3/2024 11124
      Bangalore 6/4/2024 11607
      Bangalore 6/5/2024 11321
      Bangalore 6/6/2024 11519
      Bangalore 6/7/2024 11770
      Bangalore 6/8/2024 11537
      Bangalore 6/9/2024 6452
      Bangalore 6/10/2024 11184
      Bangalore 6/11/2024 11502
      Bangalore 6/12/2024 11472
      Bangalore 6/13/2024 11930
      Bangalore 6/14/2024 11924
      Bangalore 6/15/2024 11478
      Bangalore 6/16/2024 3445
      Bangalore 6/17/2024 10465
      Bangalore 6/18/2024 11255
      Bangalore 6/19/2024 11317
      Bangalore 6/20/2024 11752
      Bangalore 6/21/2024 11936
      Bangalore 6/22/2024 11139
      Bangalore 6/23/2024 2224
      Bangalore 6/24/2024 10548
      Bangalore 6/25/2024 11663
      Bangalore 6/26/2024 12685
      Bangalore 6/27/2024 9941
      Bangalore 6/28/2024 10578
      Bangalore 6/29/2024 11184
      Bangalore 6/30/2024 6952
      Hosur 6/1/2024 5472
      Hosur 6/2/2024 10114
      Hosur 6/3/2024 10904
      Hosur 6/4/2024 11076
      Hosur 6/5/2024 10744
      Hosur 6/6/2024 10678
      Hosur 6/7/2024 10848
      Hosur 6/8/2024 10578
      Hosur 6/9/2024 8162
      Hosur 6/10/2024 10350
      Hosur 6/11/2024 10734
      Hosur 6/12/2024 11358
      Hosur 6/13/2024 11220
      Hosur 6/14/2024 10962
      Hosur 6/15/2024 6652
      Hosur 6/16/2024 10992
      Hosur 6/17/2024 11198
      Hosur 6/18/2024 11064
      Hosur 6/19/2024 11166
      Hosur 6/20/2024 9800
      Hosur 6/21/2024 11352
      Hosur 6/22/2024 9000
      Hosur 6/23/2024 2280
      Hosur 6/24/2024 8790
      Hosur 6/25/2024 11430
      Hosur 6/26/2024 11484
      Hosur 6/27/2024 11682
      Hosur 6/28/2024 11424
      Hosur 6/29/2024 11222
      Hosur 6/30/2024 2496

      • divyed's avatar
        divyed
        Super User
        1 year ago

        Hello YashLele ,

         

        Thank you for giving more details , here is updated dax for you. Please change field names

         

        // Add new column with below dax

        Total_Units_Sum =
        VAR CurrentCity = Table1[city]  
        RETURN
        CALCULATE(
            SUM(Table2[Total_Unit]),
            FILTER(
                Table2,
                Table2[city] = CurrentCity &&
                Table2[date] IN VALUES(Table1[date])
            )
        )
         
        Did I answer your query ? Mark this as solution if this helps. Kudos are appreciated.
         
        Cheers
        Neeraj Kumar

         

        Cheers