Forum Discussion
gumis_rulez
Helper I
2 years agoNeed 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
- Jihwan_Kim
Super User
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
Resolver IV
Hi,
Something like this ?
I just add this code to your Temp2 :
var temp3= ADDCOLUMNS(temp2,"@Cumul",var Activyear=[Year]var seg=[Segment]RETURNSUMX(filter(temp2,[Year]<=Activyear&&[Segment]=seg),[TOTAL]))Returntemp3 order by [Segment],[Year]