Need help with summarized table

[gumis_rulez]

Hi,

I am trying to make a new table using dax.

I am almost there but cannot make the last column. The last column should sum the values from total: for current year + previous years. (meaning for 2010, it should sum 2009 and 2010; for 2011 it should sum 2009, 2010, 2011). How I can achieve this:

The dax formula as follows:

All test =

VAR CustomerOrders = ADDCOLUMNS(

    SUMMARIZE(

        Orders,

        Orders[Klient_Nazwa],

        Orders[Data Zamówienia],

        "Year", YEAR(Orders[Data Zamówienia]),

        "TotalSales", SUM(Orders[Sprzedaż]),

        "TotalOrders", DISTINCTCOUNT(Orders[ ID Zamówienia])

    ),

    "TotalOrdersByClient",

    CALCULATE(

        SUMX(

            SUMMARIZE(

                Orders,

                Orders[Klient_Nazwa],

                "TotalOrders", DISTINCTCOUNT(Orders[ ID Zamówienia])

            ),

            [TotalOrders]

        ),

        ALLEXCEPT(Orders, Orders[Klient_Nazwa])

    ),

    "Segment",

    IF(

        CALCULATE(

            SUMX(

                SUMMARIZE(

                    Orders,

                    Orders[Klient_Nazwa],

                    "TotalOrders", DISTINCTCOUNT(Orders[ ID Zamówienia])

                ),

                [TotalOrders]

            ),

            ALLEXCEPT(Orders, Orders[Klient_Nazwa])

        ) <= 'Customer Segmentation 2'[Customer Segmentation 2 Value],

        "New",

        "Returning"

    )

)

VAR CustomerOrdersGrouped =

        GROUPBY(

            FILTER(CustomerOrders, [Segment] = "Returning"),

            [Year],

            "TOTAL", SUMX(CURRENTGROUP(), [TotalSales])

        )

VAR Final =

    ADDCOLUMNS(

        CustomerOrdersGrouped,

        "Cummulative",

        CALCULATE(

            SUMX(CustomerOrdersGrouped, [TOTAL]),

            FILTER(

                CustomerOrdersGrouped,

                [Year] <= EARLIER([Year])

            )

        )

    )

RETURN Final

Link to test file: test file 

[Jihwan_Kim · Accepted solution]

Hi,

I am not sure about the logic of creating [Total] column in the new table, but please try something like below to have one more column in the new table.

EVALUATE
	VAR CustomerOrders = ADDCOLUMNS(
		SUMMARIZE(
			Orders,
			Orders[Klient_Nazwa],
			Orders[Data Zamówienia],
			"Year", YEAR(Orders[Data Zamówienia]),
			"TotalSales", SUM(Orders[Sprzedaż]),
			"TotalOrders", DISTINCTCOUNT(Orders[ ID Zamówienia])
		),
		"TotalOrdersByClient", CALCULATE(
			SUMX(
				SUMMARIZE(
					Orders,
					Orders[Klient_Nazwa],
					"TotalOrders", DISTINCTCOUNT(Orders[ ID Zamówienia])
				),
				[TotalOrders]
			),
			ALLEXCEPT(
				Orders,
				Orders[Klient_Nazwa]
			)
		),
		"Segment", IF(
			CALCULATE(
				SUMX(
					SUMMARIZE(
						Orders,
						Orders[Klient_Nazwa],
						"TotalOrders", DISTINCTCOUNT(Orders[ ID Zamówienia])
					),
					[TotalOrders]
				),
				ALLEXCEPT(
					Orders,
					Orders[Klient_Nazwa]
				)
			) <= 2,
			"New",
			"Returning"
		)
	)

	VAR CustomerOrdersGrouped = GROUPBY(
		FILTER(
			CustomerOrders,
			[Segment] = "New"
		),
		[Year],
		"TOTAL",
		SUMX(
			CURRENTGROUP(),
			[TotalSales]
		)
	)
	RETURN
		ADDCOLUMNS(
			CustomerOrdersGrouped,
			"Cumulative", SUMX(
				FILTER(
					CustomerOrdersGrouped,
					[Year] <= EARLIER([Year])
				),
				[TOTAL]
			)
		)

[JamesFR06]

Hi,

Something like this ?

I just add this code to your Temp2 :

var temp3= ADDCOLUMNS(temp2,

"@Cumul",

var Activyear=[Year]

var seg=[Segment]

RETURN

SUMX(filter(temp2,[Year]<=Activyear&&[Segment]=seg),[TOTAL])

)

Return

temp3 order by [Segment],[Year]