cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Frequent Visitor

Adding subtotals by categories as a column

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 Annotation 2020-01-22 130401.jpg

1 ACCEPTED SOLUTION
Super User II
Super User II

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


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

View solution in original post

6 REPLIES 6
Super User II
Super User II

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


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

View solution in original post

Frequent Visitor

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

@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


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

thanks @az38 , it worked perfectly 

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

@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


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

that totally makes sense, many thanks @az38 

Helpful resources

Announcements
secondImage

Happy New Year from Power BI

This is a must watch for a message from Power BI!

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors