Sum of Certain Rows

[Anonymous]

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!!

[JarroVGIT · Accepted solution]

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 🙂

[JarroVGIT · Accepted solution]

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 🙂

[Tahreem24]

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.

[Tahreem24 · Accepted solution]

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 & &

[Tahreem24]

Sorry for type.. Instead of measure create column and use formula which mentioned by me in previous post.

[Anonymous]

 @Tahreem24 and @JarroVGIT these both work, thank you so much from keeping me from pulling my hair out!!

[JarroVGIT]

@Anonymous  I don't want to be a posterboy for bad sportmandship but @Tahreem24 solution cannot possible work as a measure. That would only work as a calculated column.

EDIT: Apologies @Tahreem24; just read your last post 🙂

[Tahreem24]

djerro123, if you read my latest post so I mentioned it was a typo mistake(mistype measure) . Else solution is workable for calculated column.

[Anonymous]

Should there be a closed parenthesis after Earlier(Table[Datecolumn ] )? I keep on getting an error: Too many arguments were passed to the FILTER function. The maximum argument count for the function is 2.