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

 

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

2 Replies

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