Cumulative sum of count of ID in stacked column chart

[syuu]

Hi,

I have a table contains ID, Week, Label

Type	Week	Label
A	W1	Y
A	W1	Y
B	W1	Y
B	W2	Y
B	W2	N
C	W2	N
B	W3	Y
C	W3	Y

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!!

[v-xuxinyi-msft · Accepted solution]

Hi @syuu 

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:

matrix visual:

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

conditional formatting -> Background color

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]

@syuu , 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])
)
)

[syuu]

@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!!

[bhanu_gautam]

@syuu , 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
)
)

[syuu]

@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