Forum Discussion

planc7's avatar
planc7
Helper I
2 years ago
Solved

Cumulative Total with Variable Column

Hi all, I'm encountering difficulties in finding the right formula to calculate Running Total on measure that comes from a virtual column. Just to let you know, this is the formula behind thi...
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi planc7 ,

    I create a table as you mentioned.

    Then I do some changes in your DAX codes. I delete SELECTEDVALUE function and here is the DAX code.

     

    test =
VAR _Table =
    CALCULATETABLE (
        ADDCOLUMNS (
            'PHISICAL_TABLE',
            "@Key",
                IF (
                    NOT ISBLANK ( PHISICAL_TABLE[Actual/Planned] ),
                    PHISICAL_TABLE[Year] + 1
                        & FORMAT ( PHISICAL_TABLE[Week], "00" ) & 'PHISICAL_TABLE'[Brand] & 'PHISICAL_TABLE'[Cluster],
                    PHISICAL_TABLE[Key Brand]
                )
        ),
        REMOVEFILTERS ( PHISICAL_TABLE[Year], PHISICAL_TABLE[Year&Week] )
    )
VAR _Table1 =
    FILTER (
        ADDCOLUMNS (
            ADDCOLUMNS (
                _Table,
                "@Actual/PlannedPY",
                    IF (
                        ISBLANK ( PHISICAL_TABLE[Actual/Planned] ),
                        CALCULATE (
                            MAX ( PHISICAL_TABLE[Actual/Planned] ),
                            FILTER ( _Table, [@Key] = EARLIER ( [@Key] ) )
                        )
                    )
            ),
            "@Actual/PlannedPY2",
                IF (
                    PHISICAL_TABLE[Year] = VALUE ( LEFT ( [@Key], 4 ) ),
                    [@Actual/PlannedPY],
                    BLANK ()
                )
        ),
        NOT ISBLANK ( [@Actual/PlannedPY2] )
    )
RETURN
    SUMX ( _Table1, [@Actual/PlannedPY2] )

     

    Finally you will see what you want.

     

     

     

    Best Regards

    Yilong Zhou

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

  • planc7's avatar
    planc7
    Helper I
    2 years ago

    Ok, really interesting! But I can't use visual calculation in a line chart with even the secondary axis populated or in a Line and Stacked Column Chart. The visual calculation option become, unfortunately, greyed-out.

    • planc7's avatar
      planc7
      Helper I
      2 years ago

      Nobody has a solution please? Thank you in advance!

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        Hi planc7 ,

        I create a table as you mentioned.

        Then I do some changes in your DAX codes. I delete SELECTEDVALUE function and here is the DAX code.

         

        test =
VAR _Table =
    CALCULATETABLE (
        ADDCOLUMNS (
            'PHISICAL_TABLE',
            "@Key",
                IF (
                    NOT ISBLANK ( PHISICAL_TABLE[Actual/Planned] ),
                    PHISICAL_TABLE[Year] + 1
                        & FORMAT ( PHISICAL_TABLE[Week], "00" ) & 'PHISICAL_TABLE'[Brand] & 'PHISICAL_TABLE'[Cluster],
                    PHISICAL_TABLE[Key Brand]
                )
        ),
        REMOVEFILTERS ( PHISICAL_TABLE[Year], PHISICAL_TABLE[Year&Week] )
    )
VAR _Table1 =
    FILTER (
        ADDCOLUMNS (
            ADDCOLUMNS (
                _Table,
                "@Actual/PlannedPY",
                    IF (
                        ISBLANK ( PHISICAL_TABLE[Actual/Planned] ),
                        CALCULATE (
                            MAX ( PHISICAL_TABLE[Actual/Planned] ),
                            FILTER ( _Table, [@Key] = EARLIER ( [@Key] ) )
                        )
                    )
            ),
            "@Actual/PlannedPY2",
                IF (
                    PHISICAL_TABLE[Year] = VALUE ( LEFT ( [@Key], 4 ) ),
                    [@Actual/PlannedPY],
                    BLANK ()
                )
        ),
        NOT ISBLANK ( [@Actual/PlannedPY2] )
    )
RETURN
    SUMX ( _Table1, [@Actual/PlannedPY2] )

         

        Finally you will see what you want.

         

         

         

        Best Regards

        Yilong Zhou

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