cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Anonymous
Not applicable

## Running Sum on If statement in Power BI Table

Hello. I have a measure that is using an if statement for the result by month. I need a running sum of that monthly column

MonthlyCommEarnedCalc =
IF( [% Month Target] >1.0,
[Monthly Comm],
[MonthlyCommCalc]
)
I have tried several different ways to create the running sum and cant get it to work. Any help would be appreciated.
The calculation below is giving me the same value as the original measure.
RunSum Month Comm Earned =
CALCULATE(
[MonthlyCommEarnedCalc],
FILTER(
ALLSELECTED(Transactions[Fiscal Month]),
MIN(Transactions[Fiscal Month start Date]) <= MAX(Transactions[Fiscal Month End Date])

))
1 ACCEPTED SOLUTION
Resolver II

Hi @Anonymous

First, create the month column in numeric form.

To calculate the running total for another measure then just add that measure code ( if block ) into your running total code.

example code :

``````Monthly Comm running sum =
VAR month = MAX(Transactions[Fiscal Month])
VAR result =  CALCULATE(
[MonthlyCommEarnedCalc],// OR Put if condtion measure code here
Transactions[Fiscal Month] <= month,
ALLSELECTED(Transactions)
)

Return result``````

check this tutorial :

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

4 REPLIES 4
Resolver II

Hi @Anonymous

First, create the month column in numeric form.

To calculate the running total for another measure then just add that measure code ( if block ) into your running total code.

example code :

``````Monthly Comm running sum =
VAR month = MAX(Transactions[Fiscal Month])
VAR result =  CALCULATE(
[MonthlyCommEarnedCalc],// OR Put if condtion measure code here
Transactions[Fiscal Month] <= month,
ALLSELECTED(Transactions)
)

Return result``````

check this tutorial :

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Anonymous
Not applicable

Thanks. Im new to Dax and im not sure exactly how to proceed.

Super User

@Anonymous What happens if you change your ALLSELECTED to ALL?

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...
Super User

@Anonymous This looks like a measure aggregation problem. See my blog article about that here: https://community.powerbi.com/t5/Community-Blog/Design-Pattern-Groups-and-Super-Groups/ba-p/138149

The pattern is:
MinScoreMeasure = MINX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
MaxScoreMeasure = MAXX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
SumScoreMeasure = SUMX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
etc.

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

#### Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors