cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mattcarter865
Helper I
Helper I

SUMX over multiple columns - Is this the optimal syntax?

I'm trying to design a measure that sums a constant value for each distinct set of values involving multiple columns in my data model.

 

This is similar to the formula I ended up with.

 

'Sales'[Earned Promotions] =
SUMX (
SUMMARIZE (
FILTER ( Sales, Sales[Quantity] = 1 ),
Promotion[Promotion Code],
'Date'[Calendar Year Month]
),
( 1 / 12 )
)

 

Running sample here

https://dax.do/tM4fkfNewjz98s/

 

The intent is to add 1/12th for each month a promotion is going on but only for sales involving a single quantity. (I work in insurance. This is a contrived example meant to mimic calculation of earned policy years.)

 

My formula works, but I am unsure it is the optimal way to structure it. I tried swapping the order so that SUMMARIZE was called from within FILTER, but performance seemed about the same. 

 

I'm still new to DAX and trying to navigate the overly numerous ways to manipulate tables.

1 ACCEPTED SOLUTION

@mattcarter865 

 

I checked your sample data and found no problem. You built the eligible virtual table with SUMMARIZE() function and then used SUMMARIZECOLUMNS() function to return the table with the fields you need. And you don't need to ADDCOLUMNS(It will preserve the context of the original table) and CALCULATETABLE(You mainly rely on relationships rather than specific conditions to filter in addition to the original table) functions.

 

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.
If not, please feel free to ask me.
 
Best Regards,
Community Support Team _ Janey

 

View solution in original post

5 REPLIES 5
v-janeyg-msft
Community Support
Community Support

Hi, @mattcarter865 

 

If the result is what you want, there is no need to modify anything.

 

Usually, First add a 'value' column in summarize function then accumulate it, which is more in line with the writing specification.

Measure =
SUMX ( SUMMARIZE ( 'Table', [Column1], [Column2], "Value", 1 / 12 ), [Value] )

 I don't understand why you want to accumulate 1/12. If you can share some sample data and your expected result in the visual, maybe I can give you a different solution.

 

Best Regards,
Community Support Team _ Janey
 

Thanks. The 1/12 is not the important part. I just need to accumulate a constant value (other than 1). 

 

My original formula does work. My question is more about understanding if I'm doing it the "preferred" way. When looking through options for DAX I find it confusing to determine if I should use SUMMARIZE vs SUMMARIZECOLUMNS vs SUMMARIZE/ADDCOLUMNS vs CALCULATETABLE, etc. Plus in my particular example I need to filter the fact table on a particular value (as I showed in my example). https://dax.do/tM4fkfNewjz98s/

 

 

@mattcarter865 

 

I checked your sample data and found no problem. You built the eligible virtual table with SUMMARIZE() function and then used SUMMARIZECOLUMNS() function to return the table with the fields you need. And you don't need to ADDCOLUMNS(It will preserve the context of the original table) and CALCULATETABLE(You mainly rely on relationships rather than specific conditions to filter in addition to the original table) functions.

 

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.
If not, please feel free to ask me.
 
Best Regards,
Community Support Team _ Janey

 

amitchandak
Super User
Super User

@mattcarter865 , What filter is ensuring distinct Promotion Code and month combination?

 

I usually prefer to create a table in such a case

 

addcolumns (
SUMMARIZE (
FILTER ( Sales, Sales[Quantity] = 1 ),
Promotion[Promotion Code],
'Date'[Calendar Month end date] //  using month end date
),"Value", ( 1 / 12 ) )

 

and join it in the data model with a date table Promotion table .

 

But you code seem fine to me

Ultimately, I'm designing a measure here. I don't want to add a new table to the data model. 

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.

Top Solution Authors
Top Kudoed Authors