ADDCOLUMN subtotal in DAX

[julien-rivley]

Hello,

Sorry if this was already replied in a previous post but I could not find anything... While creating a dax table, I can't find any way to add the column "Subtotal" which is the sum of the value for the same Dim1, would anyone has an idea?

Thanks,

Julien

Dim1	Dim2	Value	Subtotal
A	#1	10	60
A	#2	20	60
A	#3	30	60
B	#4	40	150
B	#5	50	150
B	#6	60	150

My code looks like this:

VAR T_1=

ADDCOLUMNS(

   CROSSJOIN(Dim1,Dim2)

   ,"Value"

   ,[Measure]

)

VAR T_2=

ADDCOLUMNS(

   T_1

   ,"Subtotal"

   ,SUMX(
      FILTER(
         T_1
         ,All(Dim1)
      )
      ,[Value]
   )
)

[julien-rivley]

Thanks a lot! I got lost with the use of "Filter" 🙂

[AlB · Accepted solution]

Hi @julien-rivley 

Assuming T_1 is correct:

VAR T_2 =
ADDCOLUMNS (
    T_1,
    "Subtotal", SUMX ( FILTER ( T_1, [Dim1] = EARLIER ( [Dim1] ) ), [Value] )
)

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers