Forum Discussion

casperlow's avatar
casperlow
Helper I
2 years ago
Solved

Dax Sum Unique ID values ones

Hi Dax Guru's
How do I perform a dax measure to calculate the sum of the cost ones for each unique ID.
My current Dax formula: Total Cost = sum(cost) but the output is 1100 which is incorrect.
The correct output should show 600 only 

See image below

 

Appreciate any input on this.
Did my research but could not find any solution that was close enough.

 

 

 

 

 

  • Gabry's avatar
    Gabry
    2 years ago

    Hello casperlow , 
    if every project has just one value, you can use max with summarize:

     

    Total = SUMX(SUMMARIZE('Table','Table'[project id], "val", MAX('Table'[unit])),[val])
    Let me know if this helps

     

4 Replies

  • Gabry's avatar
    Gabry
    Super User
    2 years ago

    Hello casperlow , 
    if every project has just one value, you can use max with summarize:

     

    Total = SUMX(SUMMARIZE('Table','Table'[project id], "val", MAX('Table'[unit])),[val])
    Let me know if this helps

     

  • casperlow's avatar
    casperlow
    Helper I
    2 years ago

    It doesn't work when I tried this Dax on creating a measure, the error im gettings is "The end of the input was reached."
    I had to tweak it a little for it to make it work on my table, I had to add a filter but overall the dax formula works. 🙂 thank you

    • Irwan's avatar
      Irwan
      Super User
      2 years ago

      hello casperlow 

       

      is this what you are looking for?

      create a new measure with following DAX.

      Total Sum = 
      var _Cat = SELECTEDVALUE('Table'[Category])
      Return
      SUMX(
          FILTER(
              ALLSELECTED('Table'),
              'Table'[Category]=_Cat
          ),
          'Table'[Unit]
      )

       

      however, if you dont put a selection in slicer, it will become blank.

       

      While put them into table will show all the value.

       

      Hope this will help.

      Thank you.

       

  • casperlow's avatar
    casperlow
    Helper I
    2 years ago

    yeah tried this it works on a table but not a on card. preferably works on both. the above solution worked but I had a filter for it to work.