Cumulative totals (rows)

[Anonymous]

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

Cumulative value by Order Date = CALCULATE(TOTALYTD(SUM('Orders 2'[Net Price]),'Date Table'[Date],"12/31"))

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?  

[mahoneypat · Accepted solution]

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

[mahoneypat]

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

[Anonymous]

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 · Accepted solution]

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]

Hi @mahoneypat   

Your formula is on the left.   The one I was previously using is on the right. 

Per my original issue, your formula correctly now totals up 2019 and 2010 at the row level....the problem is those values are not correct   (the 2019 and 2020 totals on the right are).  

For both the months and the years I am using the month and year from my date table   (because the $ value these sums are based on is tied to Order Date...and there is a direct relationship to the date table). 

Does that help?    I'm still back at my original desire to have the values in the table on the right correctly sum in the total.  

[mahoneypat]

That'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

[Anonymous]

Hi @mahoneypat    Thanks and indeed it is.  

One of the problems was that I was using the dates(date) instead of dates(year).   I fixed that, and added a MIN statment at the beginning because I was continuing to get the same value repeated all the way down the 2020 column  (after July).   In other words, starting in August, it was just repeating the July value all the way until December.    Adding the MIN statment stopped that, but....

But what appears to be happening, is that July's 2020 number is being added to August's 2019 number, and so on.   

It's effectively doing what I described above...it's just showing an empty "cell" instead of the actual value...but it is indeed adding $49,695,444 to each of the months in the 2019 column.   You can see here, that 78,355,950 + 49,695,444 = 128,051,394.     We do not have any orders at all in August (since we are still in July).      

Total YTD Sumx = IF (

    MIN ( 'Date Table'[Date] )

        <= CALCULATE ( MAX ( 'Orders 2'[Order Date] ), ALL ( 'Orders 2' ) ),

    SUMX(VALUES('Date Table'[Year]),[Net Price YTD]))

[parry2k]

@Anonymous 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.⚡

[Anonymous]

@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 · Accepted solution]

@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.⚡

[Anonymous]

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

[parry2k]

@Anonymous I think you need to define what you are trying to achieve, your problem was that total doesn't work and now what you are mentioning is totally different stuff. Unfortunately, DAX calculations don't work like that. You have to be very specific about what you want to achieve. What you are now asking is totally different?

[Anonymous]

Hi @parry2k   

My mistake!   I was not looking at it correctly.    But yes, its clear now.    Thanks for your help!