Forum Discussion

matthewchilton's avatar
matthewchilton
Advocate I
8 years ago
Solved

Summarzing a table variable?

I have a table value in a variable and would like to summarize it, grouping some columns and summing others. Does anyone know a nice way to achieve it?   Here's what I've tried so far.   I can...
  • v-sihou-msft's avatar
    v-sihou-msft
    8 years ago

    matthewchilton

     

    In DAX, it can't determine the current context since the row context is not generated yet when resolving the expression. You have to use a calculated column formula to calculate the results.

     

    For your requirement, I think the easiest way is creating a calculated table instead of table variable. Then summarize this calculated table.

     

    Regards,

  • matthewchilton's avatar
    matthewchilton
    8 years ago

    fyi - if anyone is looking at this facing the same challenge, it can be solved easily using GROUPBY and CURRENTGROUP().

     

     

    EVALUATE
VAR cars = DATATABLE(
	"Maker", STRING, "Sales", INTEGER, {
		{ "Ford", 300 },
		{ "Jaguar", 180 },
		{ "Jaguar", 1 }
	})
	
RETURN
GROUPBY(
	cars,
	[Maker],
	"total sales",
	SUMX(CURRENTGROUP(),[Sales])
)

     

matthewchilton's avatar
matthewchilton
Advocate I
8 years ago

fyi - if anyone is looking at this facing the same challenge, it can be solved easily using GROUPBY and CURRENTGROUP().

 

 

EVALUATE
VAR cars = DATATABLE(
	"Maker", STRING, "Sales", INTEGER, {
		{ "Ford", 300 },
		{ "Jaguar", 180 },
		{ "Jaguar", 1 }
	})
	
RETURN
GROUPBY(
	cars,
	[Maker],
	"total sales",
	SUMX(CURRENTGROUP(),[Sales])
)

 

  • filip_ai's avatar
    filip_ai
    Advocate I
    3 years ago

    Excellent, Matthew - this saved my day (or at least solved the issue I had, which could not be resolved by a calc. column).

     

    Cudos to you for posting your solution for the internet to consume 🙂