Forum Discussion

gumis_rulez's avatar
gumis_rulez
Icon for Helper I rankHelper I
2 years ago
Solved

Need help with summarized table

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 fo...
  • Jihwan_Kim's avatar
    2 years ago

    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]
    			)
    		)