Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

Cumulative sum of count of ID in stacked column chart

Hi,

I have a table contains ID, Week, Label

TypeWeekLabel
AW1Y
AW1Y
BW1Y
BW2Y
BW2N
CW2N
BW3Y
CW3Y


I want to use this table to create a matrix visual, which value is cumulative number of count of ID every week when Label = "Y".
So every week's value is the total of Labels = "Y" before that week, and I want it can show different color by different Type.

How can I do that? Any answer will be helpful!!

1 ACCEPTED SOLUTION
v-xuxinyi-msft
Community Support
Community Support

Hi @Anonymous 

 

Thanks for the reply from @bhanu_gautam .

 

Here is the testing I did, hope it helps.

 

Create two measures as follow

count yes = CALCULATE(COUNTROWS('Table'), 'Table'[Label] = "Y")

 

cumulative = CALCULATE([count yes], FILTER(ALLSELECTED('Table'[Week]), 'Table'[Week] <= MAX('Table'[Week])))

 

Since I noticed that your title mentioned stacked column chart, but your text mentioned matrix visual, I did both.

 

stacked column chart:

vxuxinyimsft_0-1722824066984.png

 

matrix visual:

vxuxinyimsft_1-1722824111447.png

 

If you need to display different types and colors in the matrix, you need to make the following settings.

 

conditional formatting -> Background color

vxuxinyimsft_3-1722824242786.png

 

vxuxinyimsft_2-1722824133216.png

 

Best Regards,
Yulia Xu

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

5 REPLIES 5
v-xuxinyi-msft
Community Support
Community Support

Hi @Anonymous 

 

Thanks for the reply from @bhanu_gautam .

 

Here is the testing I did, hope it helps.

 

Create two measures as follow

count yes = CALCULATE(COUNTROWS('Table'), 'Table'[Label] = "Y")

 

cumulative = CALCULATE([count yes], FILTER(ALLSELECTED('Table'[Week]), 'Table'[Week] <= MAX('Table'[Week])))

 

Since I noticed that your title mentioned stacked column chart, but your text mentioned matrix visual, I did both.

 

stacked column chart:

vxuxinyimsft_0-1722824066984.png

 

matrix visual:

vxuxinyimsft_1-1722824111447.png

 

If you need to display different types and colors in the matrix, you need to make the following settings.

 

conditional formatting -> Background color

vxuxinyimsft_3-1722824242786.png

 

vxuxinyimsft_2-1722824133216.png

 

Best Regards,
Yulia Xu

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

bhanu_gautam
Super User
Super User

@Anonymous , You can create a calculated column for this

 

CumulativeCount =
CALCULATE(
COUNTROWS('YourTable'),
FILTER(
ALL('YourTable'),
'YourTable'[Label] = "Y" &&
'YourTable'[Week] <= EARLIER('YourTable'[Week]) &&
'YourTable'[Type] = EARLIER('YourTable'[Type])
)
)




Did I answer your question? Mark my post as a solution! And Kudos are appreciated

Proud to be a Super User!




LinkedIn






Anonymous
Not applicable

@bhanu_gautam Hi! Thank you for the quick reply.
I have tried your answer, but some parts shows warning:
'YourTable'[Week] <= EARLIER('YourTable'[Week]) &&
'YourTable'[Type] = EARLIER('YourTable'[Type])
Warning says: EARLIER/EARLIEST refers to an earlier row context which doesn't exist.
Could you please help me to fix it? Thanks a lot!!

@Anonymous , Update measure

 

CumulativeCount =
VAR CurrentWeek = 'YourTable'[Week]
VAR CurrentType = 'YourTable'[Type]
RETURN
CALCULATE(
COUNTROWS('YourTable'),
FILTER(
ALL('YourTable'),
'YourTable'[Label] = "Y" &&
'YourTable'[Week] <= CurrentWeek &&
'YourTable'[Type] = CurrentType
)
)




Did I answer your question? Mark my post as a solution! And Kudos are appreciated

Proud to be a Super User!




LinkedIn






Anonymous
Not applicable

@bhanu_gautam Hi,

It's still not working. 
If I delete the following dax, it can work but not the result I want.
'YourTable'[Week] <= CurrentWeek &&
'YourTable'[Type] = CurrentType

Helpful resources

Announcements
September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.