Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
matthewchilton
Advocate I
Advocate I

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 use SUMMARIZE to group by columns, but I run into trouble trying to use SUM to sum up columns:

 

eg1.png

 

I tried giving the columns in my temporary table the name of a real physical table in my model, but it didn't help:

eg2.png

 

If I use SUMX I can sum up the whole column, but the values that I am grouping by don't seem to make their way into the filter context, so I end up with the grand total on every row:

eg3.png

 

Now in this toy example I can get the right answers by brute-force filtering every row:

eg5.png

But in my real-world use case, my table variable has hundreds of thousands of rows and I need to group by ~20 columns, and so this filtering will be really cumbersome to author and maintain, let alone slow to run.

 

 

Any ideas for achieving this in a simple and fast way?

 

Thanks for any tips!

2 ACCEPTED SOLUTIONS
v-sihou-msft
Employee
Employee

@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,

View solution in original post

matthewchilton
Advocate I
Advocate I

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

 

View solution in original post

6 REPLIES 6
matthewchilton
Advocate I
Advocate I

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

 

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 🙂

v-sihou-msft
Employee
Employee

@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,

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!

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" 

Thanks very much for the reply.  Sounds like a sensible suggestion.   Awkwardly for me, we are allowing the analyst to enter some parameters on the fly for the query, and so I'm not able to pre-compute the result in calculted columns or a calculated table.

I was really intrigued by your comment that "the row context is not generated yet when resolving the expression".  I've read loads about DAX and done lots of experimentation, but hadn't come across this knowledge before.  Is there a book or some kind of online resource I can consult for learning DAX at this level of detail...?

 

Thanks again!

Matt.

Helpful resources

Announcements
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors