Forum Discussion

mattcarter865's avatar
mattcarter865
Icon for Helper I rankHelper I
4 years ago
Solved

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'[...
DAX
sumx
  • v-janeyg-msft's avatar
    v-janeyg-msft
    4 years ago

    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's avatar
amitchandak
Icon for Super User rankSuper User
4 years ago

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

mattcarter865's avatar
mattcarter865
Icon for Helper I rankHelper I
4 years ago

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