cancel
Showing results 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

Helper I

## Cumulative sum of count of ID in stacked column chart

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

1 ACCEPTED SOLUTION
Community Support

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.

5 REPLIES 5
Community Support

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.

Super User

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

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

Proud to be a Super User!

Helper I

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

Super User

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

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

Proud to be a Super User!

Helper I

@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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors