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

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's avatar
psmithAPS
Regular Visitor
1 year ago

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's avatar
    Anonymous
    Not applicable
    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.