Forum Discussion
Cumulative totals (rows)
Hello - I have a date table.
I have put the following measure on a matrix visual and the 2019 and 2020 column totals are correct (the totals at the bottom of each column).
What is not correct is the row totals. The row total just shows the total of the 2020 data. For example:
2019 2020 Total
5,300 3,400 3,400
1,000 2,000 2,000
__________________________
6,300 5,400 5,400
Any idea why the total is not summing up both 2019 and 2020 and how to get it to do so?
What is the name of the column you used to give the Year values to the matrix (the field in the Columns field well). I assumed you have a column that has year values in it. Do you have an auto date hierarchy (i.e., you get .[Day], .[Month], etc. after your Date columns?). If not already, please put the same column that is currently on your matrix visual to get Year values inside the VALUES( ) in that measure.
Regards,
Pat
Anonymous this is what I will do, based on my measures, you can apply the same to your measure or the one you are using
DYTD = IF ( ISINSCOPE ( 'Calendar'[Year] ), TOTALYTD ( [Sales], 'Calendar'[Date] ), [Sales] ) * DIVIDE ( [Sales], [Sales] ) DYTD = SUMX ( VALUES ( 'Calendar'[Year] ), TOTALYTD ( [Sales], 'Calendar'[Date] ) ) * DIVIDE ( [Sales], [Sales] )I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!
⚡Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.⚡
12 Replies
- mahoneypat
Microsoft Employee
Totals really aren't totals in Tables/Matrices. They are just the calculation of your measure w/o any of the column filters (in this case of a row total). To get that behavior, you can reference your existing measure in a new one like this
New Measure = SUMX(VALUES(Date[Year]), [Cumulative value by order date])
Replace Date[Year] with the Table[Column] used in columns on the matrix visual, and put that measure in place of your existing measure and you should get the correct numbers in both the Year columns and the total.
If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
Hi mahoneypat This measure seems to be removing all context of the cumulative date pattern (summing 1, then 1+1, then 1+1+1 etc by date. Instead I am getting random values that are much much bigger than they should be.
I am not clear on what table(column) to add to the columns area (where I current have the date table value)? Perhaps once I have that fixed it will solve the issue above?
- mahoneypat
What is the name of the column you used to give the Year values to the matrix (the field in the Columns field well). I assumed you have a column that has year values in it. Do you have an auto date hierarchy (i.e., you get .[Day], .[Month], etc. after your Date columns?). If not already, please put the same column that is currently on your matrix visual to get Year values inside the VALUES( ) in that measure.
Regards,
Pat