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        15/06/24
Hosur  278.00        23/06/24
Hosur  200.00        30/06/24


 table 2
city,             date,    total units used
Bangalore 6/9/2024   6452
Bangalore 6/16/2024   3445
Bangalore 6/23/2024   2224
Bangalore 6/30/2024   6952
Hosur 6/9/2024            8162
Hosur 6/15/2024          6652
Hosur 6/23/2024 2280
Hosur 6/30/2024 2496

i want the total of units used besides each date in table 1. for each month and city
there is only one entry of totals units in table 2 for each date in table 1 .

 

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

15 Replies

  • Angith_Nair's avatar
    Angith_Nair
    Continued Contributor
    1 year ago

    Hi YashLele 

     

    Go to Table 1 in Power BI and create a new calculated column with this DAX formula - 

    TotalUnitsUsed = 
LOOKUPVALUE(
    'Table 2'[total units used],
    'Table 2'[city], 'Table 1'[city],
    'Table 2'[date], 'Table 1'[date]
)
    • YashLele's avatar
      YashLele
      Regular Visitor
      1 year ago

      i want the sum of total units used on the particular dates in table 1. and when the city or month changes it resets and gives me a the total for those particular dates.

  • 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.

     

      • 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

  • 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