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

Sum values of all rows that contains the same values in determinated cells (like a matrix does)

Hi,
I wanted to create a column (used for further calculations) where I sum the hours of all rows that are part of the same [code_name] that contains a determinated string ("work")

 

I used:

 

Col = SUMx(FILTER('table','table'[code_reason]="work"&& 'table'[code_name]=EARLIER('table'[code_name])),'table'[hours])

 

It show the right results, the problem is that I have a time slicer but even if you filter the dates the value of this column dont change.
Looking for an answer I saw that I should use a measure for this, but copy pasting the formula doesnt work.

 

Also using a matrix visualization with rows: code_name, column: code_reason, values: hours already show the correct values 

biQ.png

 

My data are in this format:

code_namenamecode_reasondatehoursCOLUMN I WANT TO CREATE
prodjohnwork01/01/2021 832
prodjohnillness01/01/2021 032
prodjohnworked01/01/2021 832
prodjohnwork02/01/2021 832
prodjohnillness02/01/2021 332
prodjohnworked02/01/2021 516
techwillywork01/01/2021 816
techwillyillness01/01/2021 816
techwillyworked01/01/2021 016
techwillywork02/01/2021 816
techwillyillness02/01/2021 016
techwillyworked02/01/2021 816
proddanwork01/01/2021 832
proddanillness01/01/2021 032
proddanworked01/01/2021 832
proddanwork02/01/2021 832
proddanillness02/01/2021 332
proddanworked02/01/2021 532
2 ACCEPTED SOLUTIONS
amitchandak
Super User IV
Super User IV

@tWdex , Column will not take slicer filter. Try measure like

 

SUMx(FILTER(allselected('table'),'table'[code_reason]="work"&& 'table'[code_name]=max('table'[code_name])),'table'[hours])

 

or

SUMx(FILTER(allselected('table'),'table'[code_reason]="work"&& 'table'[code_name]=max('table'[code_name]) && 'table'[Date] in allselected('Table'[Date])),'table'[hours])



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

Surya9
Regular Visitor

Surya9_0-1619003679772.png

Use this measure, Its working for me. 
and for date create seperate date table using calendar function and relate both the tables. 
this should work

View solution in original post

2 REPLIES 2
Surya9
Regular Visitor

Surya9_0-1619003679772.png

Use this measure, Its working for me. 
and for date create seperate date table using calendar function and relate both the tables. 
this should work

View solution in original post

amitchandak
Super User IV
Super User IV

@tWdex , Column will not take slicer filter. Try measure like

 

SUMx(FILTER(allselected('table'),'table'[code_reason]="work"&& 'table'[code_name]=max('table'[code_name])),'table'[hours])

 

or

SUMx(FILTER(allselected('table'),'table'[code_reason]="work"&& 'table'[code_name]=max('table'[code_name]) && 'table'[Date] in allselected('Table'[Date])),'table'[hours])



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors