How to do add a column for cumulative sum in a matrix visual

[Woncarc]

Hi!

I'd like to know how to do something like this in a matrix visual:

I have a fact table with components sold, and a calculated column that tells you the month it has been sold.

I've tried to add a calculated column to put the month period, but it doesn't work, as it only lets me go to another level of hierarchy.

Thanks!

[v-dineshya · Accepted solution]

Hi @Woncarc ,

Please refer below sample data snap.

I have created some measures and placed in value fields.

Please refer sample output snap.

Please find the attached snaps and sample Pbix file for your reference.

If my response has resolved your query, please mark it as the "Accepted Solution" to assist others. Additionally, a "Kudos" would be appreciated if you found my response helpful.

Thank you

[v-dineshya]

Hi @Woncarc ,

Thank you for reaching out to the Microsoft Community Forum.

Please follow below steps.

1. I took sample data as Sales table ( Component, Monthsold,Quantity columns).

2. I have created "Month Group" calculated column for Sales Table.

MonthGroup =
SWITCH(
TRUE(),
Sales[MonthSold] <= 3, "1 to 3",
Sales[MonthSold] <= 6, "4 to 6",
Sales[MonthSold] <= 9, "7 to 9",
"10 to 12"
)

3. Created "Total Quantity" calculated column .

Total Quantity = SUM(Sales[Quantity]) 

4. Created "Cumulative Quantity" calculated measure .

Cumulative Quantity =
CALCULATE(
[Total Quantity],
FILTER(
ALL(Sales[MonthSold]),
Sales[MonthSold] <= MAX(Sales[MonthSold])
)
)

5. Placed "Component" field in Rows, "MonthGroup" in column and "TotalQuantity" in values

Please find below snaps of sample data and sample output.

If my response has resolved your query, please mark it as the "Accepted Solution" to assist others. Additionally, a "Kudos" would be appreciated if you found my response helpful.

Thank you

[v-dineshya]

Hi @Woncarc ,

If my response has resolved your query, please mark it as the "Accepted Solution" to assist others. Additionally, a "Kudos" would be appreciated if you found my response helpful.

Thank you

[Woncarc]

Hi.

Thanks for the solution, but I'd like to also get the individual months on the matrix visual.

For example, in your viz it should appear 

         1       2       3       1 to 3

C1     9      25     29         63

C2     0       1       2           3

[v-dineshya · Accepted solution]

Hi @Woncarc ,

Please refer below sample data snap.

I have created some measures and placed in value fields.

Please refer sample output snap.

Please find the attached snaps and sample Pbix file for your reference.

If my response has resolved your query, please mark it as the "Accepted Solution" to assist others. Additionally, a "Kudos" would be appreciated if you found my response helpful.

Thank you

[v-dineshya · Accepted solution]

Hi @Woncarc ,

If my response has resolved your query, please mark it as the "Accepted Solution" to assist others. Additionally, a "Kudos" would be appreciated if you found my response helpful.

Thank you

[v-dineshya]

Hi @Woncarc ,

If my response has resolved your query, please mark it as the "Accepted Solution" to assist others. Additionally, a "Kudos" would be appreciated if you found my response helpful.

Thank you

[ExcelMonke]

Hello,

My recommendation would be to consider using a Date Dimension table and connect it to your fact table. Within this date dim I would recommend defining your periods (e.g. Months January through March are equal to period Q1).
From there you can create an easy matrix to where your components sold easily roll up per period they are sold. This is all assuming you have a date column in your fact table.

This is what would be best practice for Power BI, but if for some reason you have limitations within your data set to where you are unable to do this, you can consider adding a calculated column for each period along the following lines:

Calculated Col 1 to 3 = 
Table[col1] + Table[col2] + Table[col3]

Repeat for each period. Again, not best practice, but it should get you what you need.