Forum Discussion
Sum of Certain Rows
First, I just want to say I'm new to Power BI and DAX for that matter. Below I've put a bit of sample data of what I'm trying to accomplish. I'm trying to sum the usage of a certain person on a given date (Total column is what I'm trying to accomplish).
But below is what I'm getting.
The DAX formula that I'm using is: Total = CALCULATE(SUM('Table'[Usage]), DISTINCT('Table'[Date]), DISTINCT('Table'[Person]))
I've been trying to solve this for the last two days and can't figure out why it's not summing the way I want it to... Please help!!
Hi Anonymous ,
Your DAX expression does indeed not lead to the expected results. The solution of Tahreem24 is also not working, as you cannot use EARLIER() in a measure. However, you can use it in a Calculated Column.
Try the following DAX in a calculated column:
Total = VAR currentRowDate = 'Table'[Date] VAR currentPerson = 'Table'[Person] RETURN CALCULATE(SUM('Table'[Usage]), FILTER(Table, 'Table'[Date] = currentRowDate && 'Table'[Person] = currentPerson))If you want to understand what this does, let me know! I wrote it out so it is the most readable for you 🙂
Kind regards
Djerro123
-------------------------------
If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.
Kudo's are welcome 🙂
- Create a measure like below :
Total = CALCULATE (SUM(Table[Usage]), Filter(Table, Table[Datecolumn]=Earlier(Table[Datecolumn ] )&&Table [Person] =Earlier (Table[Person])))
Please replace, with & &
8 Replies
Hi Anonymous ,
Your DAX expression does indeed not lead to the expected results. The solution of Tahreem24 is also not working, as you cannot use EARLIER() in a measure. However, you can use it in a Calculated Column.
Try the following DAX in a calculated column:
Total = VAR currentRowDate = 'Table'[Date] VAR currentPerson = 'Table'[Person] RETURN CALCULATE(SUM('Table'[Usage]), FILTER(Table, 'Table'[Date] = currentRowDate && 'Table'[Person] = currentPerson))If you want to understand what this does, let me know! I wrote it out so it is the most readable for you 🙂
Kind regards
Djerro123
-------------------------------
If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.
Kudo's are welcome 🙂
- Create a measure like below :
Total = CALCULATE (SUM(Table[Usage]), Filter(Table, Table[Datecolumn]=Earlier(Table[Datecolumn ] ), Table [Person] =Earlier (Table[Person])))
Please give Kudos and accept this as a solution if it helps you.- Create a measure like below :
Total = CALCULATE (SUM(Table[Usage]), Filter(Table, Table[Datecolumn]=Earlier(Table[Datecolumn ] )&&Table [Person] =Earlier (Table[Person])))
Please replace, with & &- Sorry for type.. Instead of measure create column and use formula which mentioned by me in previous post.