Forum Discussion

2NV_DB's avatar
2NV_DB
Frequent Visitor
2 years ago
Solved

Cumulative Total from a Summarised Table

Hello All,

 

I have the followed the information provided in this thread to create a summary table with a cumulative total here: https://community.fabric.microsoft.com/t5/Desktop/Cumulative-Using-Summarized-Table/td-p/1355321

However my cumulative value will not result and I don't understand why. It should be the cumulative of the Incremental Sales column.


I expect the cumulative to be 80764+208746 etc. The Incremental Sales is simply 2024-2023 month.

What am I doing wrong?
Thanks

Cumulative Total
  • Jihwan_Kim's avatar
    Jihwan_Kim
    2 years ago

    Hi,

    I am not sure how your semantic model looks like, but I tried to create a sample pbix file like below.
    Please check the below picture and the attached pbix file.

    I suggest having a [month number] column as well.

     

     

     

     

    WINDOW function (DAX) - DAX | Microsoft Learn

     

    expected result table =
VAR _t =
    ADDCOLUMNS (
        SUMMARIZE (
            consolidated_sales_actual,
            fianacial_calendar[Month],
            fianacial_calendar[Month Number]
        ),
        "2023sales",
            CALCULATE (
                SUM ( consolidated_sales_actual[sales_net] ),
                fianacial_calendar[Year] = 2023
            ),
        "2024sales",
            CALCULATE (
                SUM ( consolidated_sales_actual[sales_net] ),
                fianacial_calendar[Year] = 2024
            )
    )
VAR _diff =
    ADDCOLUMNS ( _t, "diff", [2024sales] - [2023sales] )
VAR _cumulatediff =
    ADDCOLUMNS (
        _diff,
        "cumulatedifference",
            SUMX (
                WINDOW (
                    1,
                    ABS,
                    0,
                    REL,
                    _diff,
                    ORDERBY ( fianacial_calendar[Month Number], ASC )
                ),
                [diff]
            )
    )
RETURN
    _cumulatediff

     

2 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Icon for Super User rankSuper User
    2 years ago

    Hi,

    I am not sure how your semantic model looks like, but I tried to create a sample pbix file like below.
    Please check the below picture and the attached pbix file.

    I suggest having a [month number] column as well.

     

     

     

     

    WINDOW function (DAX) - DAX | Microsoft Learn

     

    expected result table =
VAR _t =
    ADDCOLUMNS (
        SUMMARIZE (
            consolidated_sales_actual,
            fianacial_calendar[Month],
            fianacial_calendar[Month Number]
        ),
        "2023sales",
            CALCULATE (
                SUM ( consolidated_sales_actual[sales_net] ),
                fianacial_calendar[Year] = 2023
            ),
        "2024sales",
            CALCULATE (
                SUM ( consolidated_sales_actual[sales_net] ),
                fianacial_calendar[Year] = 2024
            )
    )
VAR _diff =
    ADDCOLUMNS ( _t, "diff", [2024sales] - [2023sales] )
VAR _cumulatediff =
    ADDCOLUMNS (
        _diff,
        "cumulatedifference",
            SUMX (
                WINDOW (
                    1,
                    ABS,
                    0,
                    REL,
                    _diff,
                    ORDERBY ( fianacial_calendar[Month Number], ASC )
                ),
                [diff]
            )
    )
RETURN
    _cumulatediff

     

    • 2NV_DB's avatar
      2NV_DB
      Frequent Visitor
      2 years ago

      Wow thank you! Not sure how, but this this works well! I'm not familiar with the Window function.