Forum Discussion
Cumulative totals (rows)
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.⚡
Microsoft EmployeeThat's odd. I have a sandbox model and it seems to work as intended with similar data and same expressions. Please see pic below. Can you send upload the equivalent pic from your model? Are there other columns used in the matrix? FYI that the [TotalYTD Measure] is the same as your initial measure (where I also saw the total value matching the 2020 value).
Is this the kind of result you are looking for?
Regards,
Pat
Super UserAnonymous there are couple of options for you
DYTD =
IF ( ISINSCOPE ( 'Calendar'[Year] ), TOTALYTD ( [Sales], 'Calendar'[Date] ), [Sales] )
DYTD =
SUMX ( VALUES ( 'Calendar'[Year] ), TOTALYTD ( [Sales], 'Calendar'[Date] ) )
Any above of two measures will work, [Sales] is just a SUM measure
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.⚡
parry2k Thanks Parry. Both of your formulas work, albeit in different ways which is cool.
But I am having the same issue as with Mahoney's formula mentioned in this thread.
The last 2020 number (July) is repeating all the way down (Aug, Sept, Oct). If I put a MIN statement in front of the measure (to stop the repeating numbers showing up where there should actually be blanks) they are still being added to the row totals from 2019.
- parry2k
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.⚡
parry2k Thanks Parry.
That fixed the repeating values. But still an issue with the row totals.
There are two years here. 2019 2020. The 2020 column, ends correctly in July (as we have no orders past July).
So those blanks are correct....but you can see the value of July 2020 is still being added to the other months in 2019. Not sure how to fix or why it is happening?