cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
enestkm
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
az38
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
az38
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

enestkm
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
enestkm
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
enestkm
Frequent Visitor

that totally makes sense, many thanks @az38 

Helpful resources

Announcements
MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Urdu Hindi D365 Bootcamp 768x460.png

Urdu Hindi D365 Bootcamp

Dont miss our very own April Dunnam’s The Developer Guide to the Galaxy! Find out what the Power Platform has to offer for the traditional developer.

Top Solution Authors
Top Kudoed Authors