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 fo...
- 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] ) )
Jihwan_Kim
Super User
2 years agoHi,
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]
)
)