How to get Running Total for any aggregation

[Kalaivani]

It would be a great help if anyone can advise me on below. Thanks!!

I have a Measure for which I am checking if the target of 90% is achieved or if missed when is the first miss for an ID.

Measures:

TemplateExpectations =

VAR Expectations =

    CALCULATE(AVERAGE('Template Expectations'[TempExp]) )

 RETURN

    Expectations

MissedTarget =

CALCULATE(COUNTROWS(FILTER( SUMMARIZE('Template Expectations', 'Template Expectations'[resourceid],"Temp Exp Value", [TemplateExpectations]), [Temp Exp Value] <0.9 ) ))

RunningTotalMissedTarget =

VAR runningtotal =SUMX(FILTER(ALLSELECTED('CalendarTable'[Date]),'CalendarTable'[Date] <= MAX('CalendarTable'[Date]) ),[MissedTarget])

RETURN

    IF( [MissedTarget] = 1,runningtotal,BLANK()   )

Here is what i am getting when I group by postperiod. I want the running total to be started from 1, so that I can color code yellow for running total = 1

Another issue is, when I group the data by weekbegin date, Running Total is returning 1 for all qualifying rows but not the actual running total.

Here is the same data i worked on. Please request access if you are not able to access the file. I will provide access. Thanks in advance for your help!

https://drive.google.com/file/d/1uJw6sxJOggb-sGaUoLltPI3f6P42Y57E/view

[Anonymous]

Thank you for the reply from DataNinja777 !

Hi @Kalaivani ,

Did the reply DataNinja777 offered help you solve the problem, if it helps, you can consider to accept it as a solution so that more user can refer to, or if the above ones still not help you get the expected result, please provide some raw data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It would be helpful to find out the solution. You can refer the following link to share the required info:

How to provide sample data in the Power BI Forum

And It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you for your understanding! Your time and cooperation are much valued by us. We are looking forward to hearing from you to assist further.

How to upload PBI in Community

Best regards,

Lucy Chen

[Kalaivani]

Hi @Anonymous Yes. The answer from @DataNinja777  helped me to find the solution and I accepted it as the solution.

And thanks for the input on how to upload sample data in Power BI forum and how to upload PBI in community.

[Ashish_Mathur]

Access denied message.

[Kalaivani]

Hi @Ashish_Mathur 

Could you please request access? So that I can provide it. Thanks so much!!

[Ashish_Mathur]

I would not like to share my e-mail address.  Please make the download link public.

[Kalaivani]

Hi @Ashish_Mathur sure..

Let me do it and provide you with the updated link.

Thanks!

[DataNinja777]

Hi @Kalaivani ,

To address the issues with your running total measure, you need to ensure that the running total starts from 1 when the first missed target occurs for an ID and continues to increment with subsequent misses. Additionally, the measure should handle grouping by different periods, such as week or month, without resetting the running total for each group.

First, to ensure the running total starts from 1 on the first miss, you need to calculate the first occurrence of a missed target and then increment the running total from that point. Below is an updated version of your RunningTotalMissedTarget measure:

RunningTotalMissedTarget =
VAR CurrentDate = MAX('CalendarTable'[Date])
VAR FirstMissDate =
    CALCULATE(
        MIN('CalendarTable'[Date]),
        FILTER(
            'Template Expectations',
            [TemplateExpectations] < 0.9
        )
    )
VAR RunningTotal =
    CALCULATE(
        COUNTROWS(FILTER('Template Expectations', [TemplateExpectations] < 0.9)),
        'CalendarTable'[Date] <= CurrentDate
    )
RETURN
    IF(CurrentDate >= FirstMissDate, RunningTotal, BLANK())

This measure calculates the minimum date when a missed target occurs and ensures the running total starts incrementing from that date onward. If there is no missed target before the current date, the measure returns a blank.

Next, to handle grouping by periods such as weekbegin or postperiod, it’s important to ensure that the running total calculation respects the date context within the grouping. Below is a modified version of the running total measure to handle grouped views:

RunningTotalMissedTargetGrouped =
VAR CurrentDate = MAX('CalendarTable'[Date])
VAR FirstMissDate =
    CALCULATE(
        MIN('CalendarTable'[Date]),
        FILTER(
            'Template Expectations',
            [TemplateExpectations] < 0.9
        )
    )
RETURN
    IF(CurrentDate >= FirstMissDate, COUNTROWS(FILTER(ALLSELECTED('CalendarTable'[Date]), 'CalendarTable'[Date] <= CurrentDate)), BLANK())

This version ensures that the measure works across different groupings by using ALLSELECTED to maintain the filter context of the grouped date periods.

If your report groups the data by weekly periods using a WeekBegin column, you can adjust the measure to calculate the running total based on that column. Below is an example measure for weekly grouping:

RunningTotalMissedTargetByWeek =
VAR CurrentWeek = MAX('CalendarTable'[WeekBegin])
VAR FirstMissWeek =
    CALCULATE(
        MIN('CalendarTable'[WeekBegin]),
        FILTER(
            'Template Expectations',
            [TemplateExpectations] < 0.9
        )
    )
RETURN
    IF(CurrentWeek >= FirstMissWeek, COUNTROWS(FILTER(ALLSELECTED('CalendarTable'[WeekBegin]), 'CalendarTable'[WeekBegin] <= CurrentWeek)), BLANK())

To apply color coding based on this running total measure, you can use conditional formatting in Power BI. Set the formatting rule to highlight the measure in yellow when the running total equals 1. This can be done by applying conditional formatting to the visual containing the measure, setting a rule that if the running total equals 1, it applies a yellow color.

This approach ensures that the running total measure works correctly across different grouping periods and provides a clear visual indicator of when the first missed target occurs.

Best regards,

[Kalaivani]

Hi @DataNinja777 Thanks so so so much for your response. Let me work on it and will let you know if it's working..

Thanks again!!

[Kalaivani]

Hi @DataNinja777 

I am actually using a table visualization with month year and week begin date on rows with week begin date column compressed. 

When I keep as month year alone. The running total has to group by year month

When I expand to next level, 

The running total has to group by week begin date.

Will that work on this case?

Thanks!

[DataNinja777 · Accepted solution]

Hi @Kalaivani 

Yes, it will work as you described! Here's how we can handle a dynamic running total in Power BI that adjusts automatically based on whether you're viewing the table by Year-Month or WeekBeginDate.

Your requirements are:

1. When collapsed to Year-Month, the running total should group by Year-Month.
2. When expanded to WeekBeginDate, the running total should group by WeekBeginDate.

We can achieve this with a DAX measure that checks the current hierarchy level using the ISINSCOPE function.

RunningTotalMissedTarget =
VAR FirstMissDate =
    CALCULATE(
        MIN('CalendarTable'[Date]),
        FILTER(
            ALL('Template Expectations'),
            [TemplateExpectations] < 0.9
        )
    )
RETURN
    IF(
        MAX('CalendarTable'[Date]) >= FirstMissDate,
        IF(
            ISINSCOPE('CalendarTable'[WeekBeginDate]),
            // Running total grouped by WeekBeginDate
            CALCULATE(
                COUNTROWS('Template Expectations'),
                FILTER(
                    'Template Expectations',
                    [TemplateExpectations] < 0.9 &&
                    'CalendarTable'[WeekBeginDate] <= MAX('CalendarTable'[WeekBeginDate])
                )
            ),
            // Running total grouped by Year-Month
            CALCULATE(
                COUNTROWS('Template Expectations'),
                FILTER(
                    'Template Expectations',
                    [TemplateExpectations] < 0.9 &&
                    'CalendarTable'[YearMonth] <= MAX('CalendarTable'[YearMonth])
                )
            )
        ),
        BLANK()
    )

The ISINSCOPE function checks if the current view in the table visual is at the WeekBeginDate level. If it is, the measure calculates the running total grouped by WeekBeginDate. If the view is at a higher level, such as Year-Month, the measure defaults to calculating the running total by Year-Month.

The measure starts counting from the first missed target date, using the FirstMissDate variable to ensure the running total begins only when a missed target is detected. This way, the measure adjusts dynamically based on the level of grouping in the table visual.

For example, when the table is grouped by Year-Month, the running total shows cumulative values for each month. If the table is expanded to display WeekBeginDate, the running total shows cumulative values for each week within the month. In this case, for January 2024, the running total for the month would be 5, while the weekly breakdown would be 1 for January 1, 2 for January 8, and 3 for January 15. For February 2024, the running total for the month would be 7, with 4 for February 5.

Yes, this will work for your case. The measure dynamically adjusts based on whether the table is grouped by Year-Month or expanded to WeekBeginDate in a hierarchical table visualization. There is no need for manual adjustments, as the measure handles both grouping levels seamlessly.

Best regards,

[Kalaivani]

Hi @DataNinja777 Thanks so so much! I modified your code a bit to achieve for weekly as well as monthly. Here is the updated code that I used. 

Thanks again for your input. It helped me to crack out the final code.

RunningTotalMissedTargetFinal =

VAR wbd =

            SUMX(

                FILTER(

                    ALLSELECTED('Template Expectations'[WeekBeginDate]),

                    'Template Expectations'[WeekBeginDate] <= MAX('Template Expectations'[WeekBeginDate])

                ),

                [MissedTarget]

            )

VAR monthyear =

        SUMX(

            FILTER(

                ALLSELECTED('CalendarTable'[Date]),

                'CalendarTable'[Date] <= MAX('CalendarTable'[Date])

            ),

            [MissedTarget]

        )

RETURN

IF(

    ISINSCOPE('Template Expectations'[WeekBeginDate]),

    -- If 'WeekBeginDate' is in scope, use the first measure (RunningTotalMissedTarget)

     IF(

        [MissedTarget] = 1,

        wbd,

        BLANK()  -- or you can return 0 or any other default value if needed

    ),  

    IF(

        ISINSCOPE('CalendarTable'[Year-Month]),

        -- If 'postperiod' is in scope, use the second measure (RunningTotalMissedTarget2)

        IF(

        [MissedTarget] = 1,

        monthyear,

        BLANK()  -- or you can return 0 or any other default value if needed

    ),      

        BLANK()  -- or return 0 or any other default value when neither condition is met

    )

)

MissedTarget =

CALCULATE(

    COUNTROWS(

       FILTER(

            SUMMARIZE(

                'Template Expectations',

                'Template Expectations'[ResourceID],

                "Temp Exp Value", [TemplateExpectations]

            ),

            [Temp Exp Value] <0.9

        )

    )

)