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])
)

     

lpriceFTW's avatar
lpriceFTW
Helper II
3 years ago

Can you explain this like im 5? What does it mean to generate row context when resolving the expression?

What is a calculated column formula vs the table variable? What is a calculated table and how is that different from calculated column and a table variable?

Thank you!

dg123456789's avatar
dg123456789
Advocate III
3 years ago

Welcome to the PowerBi community. Where all answers are extremely vague and without context and most "Solved" questions are to "please post this question as a new feature so i can mark this as solved even there is no actual answer"