Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
psmithAPS
Regular Visitor

Help with doing a cumulative total

Alright, so I've been banging my head against the wall for a week trying to figure out how to do something that would have taken -3 seconds in excel. 

 

I have a graph that I am creating that is simply a planned vs actual count over time. The Count is performed by power BI and is simply counting how many tasks started each month from a rip out of P6. What I cannot figure out for the life of me is how to simple create a new visual calculation in this table that is a running total of the count each month. (See table below). 

psmithAPS_0-1733428710478.png

 

1 ACCEPTED SOLUTION

Hi, @psmithAPS 

Thank you for your prompt response.

Firstly,I'm glad to hear that you're interested in visual calculations. However, I should explain that visual calculations are only applicable to report views.

vlinyulumsft_0-1733895990520.png

In table view, visible DAX calculations are either calculated columns or calculated tables:

vlinyulumsft_1-1733895990521.png

vlinyulumsft_2-1733896010237.png

If you want DAX calculations to be visible in table view, you can try the second solution I mentioned earlier, but with a slight modification:

 

1.Firstly, create a calculation table and aggregate the values:

Table = 
SUMMARIZE(
    'virtual_data',
    'virtual_data'[Date].[Year],
    'virtual_data'[Date].[Month],'virtual_data'[Date].[MonthNo],
    "cx", COUNT('virtual_data'[xx]),
    "cl", COUNT('virtual_data'[ll])
)

2.Secondly, create the following calculated column:

Column1= CALCULATE (
        SUM ( 'Table'[cl] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[MonthNo] <= EARLIER( 'Table'[MonthNo] )
                && 'Table'[Year] =EARLIER( ( 'Table'[Year] )
        )
    ))
Column 2 = CALCULATE (
        SUM ( 'Table'[cx] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[MonthNo] <= EARLIER( 'Table'[MonthNo] )
                && 'Table'[Year] =EARLIER( ( 'Table'[Year] )
        )
    ))

If you prefer to use visual calculations, you can try the first solution I mentioned earlier.

 

3.Here's my final result, which I hope meets your requirements.

vlinyulumsft_3-1733896082233.png

4.You may need to note that if the total in the matrix is not calculated in the way you desire, you can use the following measure:

MEASURE =
IF ( ISINSCOPE ( 'Table'[Year] ), MAX ( 'Table'[Column1] ), SUM ( [cl] ) )

vlinyulumsft_4-1733896138596.png

Please find the attached pbix relevant to the case.

 

Best Regards,

Leroy Lu

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

 

View solution in original post

6 REPLIES 6
v-linyulu-msft
Community Support
Community Support

Thanks for the reply from FreemanZ , please allow me to provide another insight:

Hi, @psmithAPS 

Based on your requirements, I am providing three solutions:

 

Here is my sample data:

vlinyulumsft_0-1733721659194.png

Solution 1: Direct Visual Calculation

vlinyulumsft_1-1733721659195.png

Result:

vlinyulumsft_4-1733721708067.png

Although this is the simplest method, it cannot remove the original column and requires calculations based on the original data.

vlinyulumsft_5-1733721708070.png

For more details, please refer to:

Using visual calculations in Power BI Desktop - Power BI | Microsoft Learn

 

Solution 2:

1.Firstly, create the following calculation table:

Table = 
SUMMARIZE(
    'virtual_data',
    'virtual_data'[Date].[Year],
    'virtual_data'[Date].[Month],'virtual_data'[Date].[MonthNo],
    "cx", COUNT('virtual_data'[xx]),
    "cl", COUNT('virtual_data'[ll])
)

2.Secondly, create the following measure:

MEASURE = 
IF (
    ISINSCOPE ( 'Table'[Year] ),
    CALCULATE (
        SUM ( 'Table'[cl] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[MonthNo] <= MAX ( 'Table'[MonthNo] )
                && 'Table'[Year] = MAX ( 'Table'[Year] )
        )
    ),
    SUM ( 'Table'[cl] )
)
Measure2 = 
IF (
    ISINSCOPE ( 'Table'[Year] ),
    CALCULATE (
        SUM ( 'Table'[cx] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[MonthNo] <= MAX ( 'Table'[MonthNo] )
                && 'Table'[Year] = MAX ( 'Table'[Year] )
        )
    ),
    SUM ( 'Table'[cx] )
)

3.Result:

vlinyulumsft_6-1733721790726.png

This solution can remove the original data column and clearly show the data before accumulation. However, it involves creating a new table, which may not be suitable for large datasets.

 

Solution 3:

1.Create the following measure:

Measurexx = 
VAR c1 =
    SUMMARIZE (
        ALLSELECTED ( 'virtual_data' ),
        'virtual_data'[Date].[Year],
        'virtual_data'[Date].[Month],
        'virtual_data'[Date].[MonthNo],
        "cx", COUNT ( 'virtual_data'[xx] ),
        "cl", COUNT ( 'virtual_data'[ll] )
    )
VAR r1 =
    SUMX (
        FILTER (
            c1,
            'virtual_data'[Date].[Year] = MAX ( 'virtual_data'[Date].[Year] )
                && 'virtual_data'[Date].[MonthNo] <= MAX ( 'virtual_data'[Date].[MonthNo] )
        ),
        [cx]
    )
RETURN
    IF ( ISINSCOPE ( 'virtual_data'[Date].[Year] ), r1, SUMX ( c1, [cx] ) )
Measurell =
VAR c2 =
    SUMMARIZE (
        ALLSELECTED ( 'virtual_data' ),
        'virtual_data'[Date].[Year],
        'virtual_data'[Date].[Month],
        'virtual_data'[Date].[MonthNo],
        "cx", COUNT ( 'virtual_data'[xx] ),
        "cl", COUNT ( 'virtual_data'[ll] )
    )
VAR r2 =
    SUMX (
        FILTER (
            c2,
            'virtual_data'[Date].[Year] = MAX ( 'virtual_data'[Date].[Year] )
                && 'virtual_data'[Date].[MonthNo] <= MAX ( 'virtual_data'[Date].[MonthNo] )
        ),
        [cl]
    )
RETURN
    IF ( ISINSCOPE ( 'virtual_data'[Date].[Year] ), r2, SUMX ( c2, [cl] ) )

2.Result:

vlinyulumsft_7-1733721842340.png

This solution is more efficient but not as easy to maintain as Solution 2.

 

You can choose based on your needs.

 

Please find the attached pbix relevant to the case.There is a page for each scenario you may need to pay attention to

vlinyulumsft_8-1733721842341.png

Best Regards,

Leroy Lu

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

 

FreemanZ
Super User
Super User

hi @psmithAPS ,

 

Supposing you have a date column, try to

 

1.add a calculated column like:

yy/mm = FORMAT(data[date], "yy/mm")

2.plot the visual with yy/mm column and a measure like:

cumulative total of actual start =

CALCULATR(

    COUNT(data[actual start]),

    data[yy/mm]<=MAX(data[yy/mm])

)

I am unsure whether I need or how to go about inputting a date column, every piece of data is tied to an ID value, and there are several dates tied to each ID, the schedule start and finish date, the actual start date and finish date, the baseline start and finish date, and the planned start and finish date. The schedule start date is what I use to get the date range of each count it is the X axis of the chart purely because it is the only date that will not skew the date data of the other types. The actual start date and the baseline start dates of each activity create the counts, just like a pivot table counting the number of activities in the schedule that fall within a set number of dates. I may not be understanding the role of a date column in Power BI.

Hi, @psmithAPS 

Thank you for your prompt response.

vlinyulumsft_0-1733808136705.png

You can try the following steps in Power Query to generate a new date column:

1.Firstly, here is my sample data:

vlinyulumsft_1-1733808136706.png

2.Secondly, I added a custom column in Power Query:

vlinyulumsft_2-1733808165851.png

Date.FromText(Text.From([Year]) & "-" & Text.PadStart(Text.From([Month]), 2, "0") & "-01")

vlinyulumsft_3-1733808215923.png

3.Next, I modified its data type:

vlinyulumsft_4-1733808215924.png

4.Below is the final result:

vlinyulumsft_5-1733808231133.png

Please find the attached pbix relevant to the case.

 

Best Regards,

Leroy Lu

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

The problem with this solution appears to be that the dates I pull from already are in mm/dd/yyyy format prior to starting the chart, there is no text date to combine until I utilize the start date as the X-axis data. Is there a visual calculation that allows for a cumulative sum within the table view of the data.

Hi, @psmithAPS 

Thank you for your prompt response.

Firstly,I'm glad to hear that you're interested in visual calculations. However, I should explain that visual calculations are only applicable to report views.

vlinyulumsft_0-1733895990520.png

In table view, visible DAX calculations are either calculated columns or calculated tables:

vlinyulumsft_1-1733895990521.png

vlinyulumsft_2-1733896010237.png

If you want DAX calculations to be visible in table view, you can try the second solution I mentioned earlier, but with a slight modification:

 

1.Firstly, create a calculation table and aggregate the values:

Table = 
SUMMARIZE(
    'virtual_data',
    'virtual_data'[Date].[Year],
    'virtual_data'[Date].[Month],'virtual_data'[Date].[MonthNo],
    "cx", COUNT('virtual_data'[xx]),
    "cl", COUNT('virtual_data'[ll])
)

2.Secondly, create the following calculated column:

Column1= CALCULATE (
        SUM ( 'Table'[cl] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[MonthNo] <= EARLIER( 'Table'[MonthNo] )
                && 'Table'[Year] =EARLIER( ( 'Table'[Year] )
        )
    ))
Column 2 = CALCULATE (
        SUM ( 'Table'[cx] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[MonthNo] <= EARLIER( 'Table'[MonthNo] )
                && 'Table'[Year] =EARLIER( ( 'Table'[Year] )
        )
    ))

If you prefer to use visual calculations, you can try the first solution I mentioned earlier.

 

3.Here's my final result, which I hope meets your requirements.

vlinyulumsft_3-1733896082233.png

4.You may need to note that if the total in the matrix is not calculated in the way you desire, you can use the following measure:

MEASURE =
IF ( ISINSCOPE ( 'Table'[Year] ), MAX ( 'Table'[Column1] ), SUM ( [cl] ) )

vlinyulumsft_4-1733896138596.png

Please find the attached pbix relevant to the case.

 

Best Regards,

Leroy Lu

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

 

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

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