Forum Discussion

psmithAPS's avatar
psmithAPS
Regular Visitor
1 year ago
Solved

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 si...
  • Anonymous's avatar
    Anonymous
    1 year ago

    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.
     

     

Anonymous's avatar
Anonymous
Not applicable
1 year ago

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.