Adding subtotals by categories as a column

[enestkm]

Hi,
I am trying to add a column to a table which calculates the subtotals by categories and repeats it on every row.
It is the last column on the right-hand side that should sum up the visit_count column by user and visit_year and write it to last column by repeating it for every user/year
I have tried SUMX but could not figure it out ,

Many thanks in advance

[az38 · Accepted solution]

Hi @enestkm 

try a measure

Measure = CALCULATE(SUM([Visit_Count]), ALLEXCEPT('Table', 'Table'[User], 'Table'[Visit_Year]))

do not hesitate to give a kudo to useful posts and mark solutions as solution

[enestkm]

thanks @az38 , however i need it as a column to use as X-axis on a visual

[az38]

@enestkm 

column with the same statement should work too. check

but measure is preffered and Im not sure your visuals will be correct in any use cases if you will use a column

do not hesitate to give a kudo to useful posts and mark solutions as solution

[enestkm]

thanks @az38 , it worked perfectly 

is this can be replicated as a custom column in query editor?

[az38]

@enestkm 

power query editor mission is data cleansing and transformation, all business logic should be calculated in DAX

maybe, you can create some M code that could calculate, but it will be not only not the best practice, but the worst practice

do not hesitate to give a kudo to useful posts and mark solutions as solution

[enestkm]

that totally makes sense, many thanks @az38