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] ) )
JamesFR06
Resolver IV
2 years agoHi,
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]