Help with doing a cumulative total

[psmithAPS]

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). 

[Rentia]

Please asssit im having a similar problem, I did a calculated Visual , for running sum but it not showing when published i need to recreate either a measure or calculated column that does the same function, please advise, Ive tried everything,. 

[Anonymous]

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:

Solution 1: Direct Visual Calculation

Result:

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

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:

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:

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

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]

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])

)

[psmithAPS]

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.

[Anonymous]

Hi, @psmithAPS 

Thank you for your prompt response.

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

1.Firstly, here is my sample data:

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

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

3.Next, I modified its data type:

4.Below is the final result:

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.

[psmithAPS]

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.

[Anonymous · 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.

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

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.

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] ) )

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.