Help with a simple cumulative sum. Newbie to Power BI.

[AlexGurovich]

Hi, I0m new to Power Bi.

Simple question: I have a table with 3 colmuns:

- TransactionId

- TransactionAmount

- TransactionMonth

I want to add a 4th column, TotalAmountofThisMonth, that sums up in each row the total $ amount of Transactions of each month.

So as an example, Januray sold $100+$200+$300 = $600. Hence, $600 should show up for column TotalAmountofThisMonth, in each  of the 3 rows that have "January" as its TransactionMonth.

What's the DAX expression for this????

TransactionId	TransactionAmount	TransactionMonth
1	$100	January
2	$200	January
3	$300	January
4	$400	February
5	$500	February
6	$600	March
7	$700	March
8	$800	March

[danextian · Accepted solution]

Hello @AlexGurovich 

If  you want to just show the monthly total, you can create simple measure that sums up the values:

TotalAmountofThisMonth =
SUM ( Table[TransactionAmount] )

If you want this to be a physical column in your data model not just a column in a table or a matrix visual, you can try this:

TotalAmountofThisMonth =
CALCULATE (
    SUM ( Table[TransactionAmount] ),
    ALLEXCEPT ( Table, Table[TransactionMonth] )
)

[kentyler]

If you import this table into Power BI and create a Matrix, it will do the totaling automatically.

To calculate the total for each month we write a measure

Month Total =

VAR current_month = selectedvalue(Transactions[TransactionMonth])

var month_total = calculate(sum(Transactions[TransactionAmount]),Transactions[TransactionMonth] = current_month, ALL(Transactions))

RETURN month_total

first we get the month in the row the measure is running in using SELECTGEDVALUE()

then we use that value as a filter to total all the values for that month, we add ALL() to the calculate statement to be able to total all the rows in the transactions table, and not just the row the measure is running in

I'm a personal Power BI trainer Every time I answer a question I learn something.

[AlexGurovich]

Excellent, thanks for the help and support. Kudos