Rolling sums, but on a text field...

[Zarlot531]

So I have built the below matrix showing the month lag it takes install orders. I plan to put this in a %-to-total view. 

But what really makes sense is to have this in a rolling format. so that, for example, one can see that by 3 months, say, 30% of orders have been installed, not just the % that was installed in month 3. The main problem I see here though is that that the "months" (Lag2) column is actually a text column. Why? Well, "12+" isn't a number, neither is "reversed." Reversed should actually not be rolled up since they never happened. 

Is this possible at all? Now that I think about it, I might be able to re-write my case statement at the SQL level to make, say, "3" equal 3 or less. Hmm

[JarroVGIT · Accepted solution]

Hi @Zarlot531 ,

See my PBIX here:

https://1drv.ms/u/s!Ancq8HFZYL_aiIkk9Sw0fCTwRWXgkw?e=Xsixhc

What did I do:

1. Create a calculated column named LagNumerical (based on Lag3 column)

2. Removed error rows from the dataset (rownumbers 621, 1004 and 1768) in Query Editor

3. Created the following measure:

Measure = 
VAR selectedLag = SELECTEDVALUE(Sheet1[LagNumerical], 100)
VAR minDate = CALCULATE(MIN(Sheet1[SoldMonth]), REMOVEFILTERS(Sheet1[LagNumerical]))
VAR maxDate = CALCULATE(MAX(Sheet1[SoldMonth]), REMOVEFILTERS(Sheet1[LagNumerical]))
RETURN
CALCULATE(SUM(Sheet1[RMR]), FILTER(ALL(Sheet1), Sheet1[LagNumerical] <= selectedLag && Sheet1[SoldMonth] >= minDate && Sheet1[SoldMonth] <= maxDate))

That seemed to have done the trick to have rolling sums of RMR 🙂

Kind regards

Djerro123

-------------------------------

If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.

Keep those thumbs up coming! 🙂

[Ashish_Mathur]

Hi,

Share some data and show the expected result.

[Zarlot531]

Here is the underlying data, with Business Name of course stripped out.

Data File Dropbox 

Here is an excel version. Basically, I'm trying to get a rolling % of installed RMR by month. It doesn't (and won't) have to look exactly to what's below, but something close to this idea. I've just having trouble with the rolling part. In Excel, it's easy, you just use SUMIFS and there ya go.

[JarroVGIT]

If it isn't a whole number field, and you can't work around that (e.g. remove 'reversed' and change 12+ in the actual numbers it represents), you could resort to SWITCH(). Something like 

Measure = 
SWITCH( 'Query1'[Lag2],
  "1", DIVIDE(CALCULATE(SUM(Query1[RMR]), FILTER(ALL(Query1), Query1[Lag2] = "1")), CALCULATE(SUM(Query1[RMR]), ALL(Query1))),
  "2", DIVIDE(CALCULATE(SUM(Query1[RMR]), FILTER(ALL(Query1), Query1[Lag2] = "1" || Query1[Lag2] = "2")), CALCULATE(SUM(Query1[RMR]), ALL(Query1))),
......
)

This can be less anoying by adding a calculated column to your mix that makes reversed 100 and 12+ a 13, so you can have simpler filters (using < rather then multiple = filters in the calculate bits of the above).

Let me know if this helps!

Kind regards

Djerro123

-------------------------------

If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.

Keep those thumbs up coming! 🙂

[Zarlot531]

So I've been playing with what you've suggested some. I created a Column that uses the switch formula to give me numbers for each month instead of text. But now I can't seem to get the original Switch formula to work correctly. In particular when I try to use the <= sign, intellisense underlines it in red. Any ideas? Thank

s for your help. 

[JarroVGIT]

What is the error you are getting when using the switch statement in the measure? <= is a valid operator, depending on where you use it.

[Zarlot531]

I resolved the intellisense error, but the percentages that were coming back were off so I decided to just try to get the dollar amounts to roll properly. I'm not sure why I'm getting such huge numbers for # 1. In the *entire* dataset, the sum of "RMR" only sums to 2.4 million. Yet somehow I'm getting numbers way bigger than that in the rollup. Interesting puzzle. I'll continue to try to resolve. Thank you!

[JarroVGIT]

The naming suggests you are adding a calculated column, not a measure ("Column 3"). The original SWITCH is meant as a measure and won't work as a calculated column. Also, do your future self a big favor and give meaningful names to your calculated columns and measures, even during development. 🙂

[Zarlot531]

The problem is, Lag2 isn't a measure. So your switch formula when I type it in doesn't recognize Lag2. I guess I somehow need to make a measure that equals the column so that I can get the switch function to work. 

[JarroVGIT]

You can use SWITCH ( SELECTEDVALUE ( Table[Lag2] ), "1", ..... to reference the current value of Lag2. You then can only use the measure in the table visual like in your example. Sorry that was my bad, should've been in the code example. 

[JarroVGIT · Accepted solution]

Hi @Zarlot531 ,

See my PBIX here:

https://1drv.ms/u/s!Ancq8HFZYL_aiIkk9Sw0fCTwRWXgkw?e=Xsixhc

What did I do:

1. Create a calculated column named LagNumerical (based on Lag3 column)

2. Removed error rows from the dataset (rownumbers 621, 1004 and 1768) in Query Editor

3. Created the following measure:

Measure = 
VAR selectedLag = SELECTEDVALUE(Sheet1[LagNumerical], 100)
VAR minDate = CALCULATE(MIN(Sheet1[SoldMonth]), REMOVEFILTERS(Sheet1[LagNumerical]))
VAR maxDate = CALCULATE(MAX(Sheet1[SoldMonth]), REMOVEFILTERS(Sheet1[LagNumerical]))
RETURN
CALCULATE(SUM(Sheet1[RMR]), FILTER(ALL(Sheet1), Sheet1[LagNumerical] <= selectedLag && Sheet1[SoldMonth] >= minDate && Sheet1[SoldMonth] <= maxDate))

That seemed to have done the trick to have rolling sums of RMR 🙂

Kind regards

Djerro123

-------------------------------

If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.

Keep those thumbs up coming! 🙂

[Zarlot531]

That's awesome, thanks so much! Only thing that would be icing on the cake would be having the capability to slice on state and job type. When i added slicers to do this, the slice didn't do anything.  But I have a feeling this is because of the custom solution required. But this is great as is, thanks again.  

[JarroVGIT]

You are very much welcome 🙂 I tweaked the measure a little so that your slicers should work:

Measure2 = 
VAR selectedLag = SELECTEDVALUE(Sheet1[LagNumerical], 100)
RETURN
CALCULATE(SUM(Sheet1[RMR]), Sheet1[LagNumerical] <= selectedLag, REMOVEFILTERS(Sheet1[LagNumerical]))

It's a bit simpler and cleaner 🙂 On another note, this has one weird side effect (as did the previous measure but I just noticed it). In 2017 there are no values for LagNumerical 11 and 12, resulting in selectedLag = 100. I was cleaning the above measure and was wrecking my brain on why that was the case but it's because of the lack of values;

This problem will grow if you apply slicers (i.e. there will be more years where there are zero rows for a given NumericalLag value) so I will think about how to solve this issue. 

Kind regards

Djerro123

-------------------------------

If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.

Keep those thumbs up coming! 🙂

[Zarlot531]

Good catch. And good point. One way I might be able to resolve this, since the data is being refreshed dynamically from SQL Server is to use a union query to add "fake" $0 sales for lag combinations that are missing. Problem is that might get messy with the slices because I'd need to really add $0 sales for any given slicer combination that is missing. I'm better with SQL than I am with DAX, so that might be one (albeit messy) solution, especially if you're growing tired of this project. But I'll take any advice you're willing to give. 🙂

Also, and this is a _very_ minor issue, but is there an easy way to rename the 13 and 100 back to '12+' and 'Reversed' ? If it's complicated, it's not worth it, as I can always just explain what those numbers represent. 

[JarroVGIT · Accepted solution]

Alright so this bugged me more than I like to admit 😛 However, I finally came up with a fix for that behaviour. To be completely honest, I don't fully understand why this is working and I wasn't able to solve this without a related table, but anyway; it works and it fits your requirements.

Basically what I did was creating a table (dim_NumLag) with values 1-13 and 100 in a single column named 'UniqueLagNums'. Then I created a 1-many relationship between dim_NumLag[UniqueLagNums] and Queri1[LagNumerical] (the calculated column). Then I changed the Matrix visual to use dim_NumLag[UniqueLagNums] as columns and changed the measure like this:

Measure2 = 
VAR selectedLag = CALCULATE(SELECTEDVALUE(dim_NumLag[UniqueLagNums], 100))
RETURN
CALCULATE(SUM(Sheet1[RMR]), Sheet1[LagNumerical] <= selectedLag, REMOVEFILTERS(dim_NumLag[UniqueLagNums]))

Resulting in the following table:

I have updated my file so you can redownload it if you like 🙂 This was fun! If you don't mind, I might write a blog post about this.

Kind regards

Djerro123

-------------------------------

If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.

Keep those thumbs up coming! 🙂

[Zarlot531]

Wow, thanks again. Doing this at the SQL level would have been a pain and am not quite sure it would have worked anyway. 

And feel free to share on your blog. Would love to follow your blog actually. 

[JarroVGIT]

Oh sorry I forgot to mention that as step 4, I created the matrix and showed the measure as percentage of grand total of row:

Kind regards

Djerro123

-------------------------------

If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.

Keep those thumbs up coming! 🙂

[Zarlot531]

Thanks! What's interesting now is that it's summing all of the 2s and 1s (in this example) for all time, instead of just for 2016, etc. 

Interesting puzzle. I'm playing with the formula now to see if I can tweak to get it to recognize where it is in the matrix with year, month, etc. I also updated some of the column/measure names per your suggestion. 

[JarroVGIT]

You can try replacing ALL(Query1) in the first CALCULATE statement with just Query1, that should return the current filtered context of that table. Or ALLEXCEPT(Query1, 'Query1'[Year])..

We are getting close! 😄

[Zarlot531]

Thanks for all your help. You are right we are getting close. This formula brings back the correct "0" column but then for the next column I would expect 65,807 + 155,318 for the 2016 row, but it's only showing the "1" column. I am continuing to tinker. I may try to go back and use your original formula. (update: so I did that (see second image), and still for some reason can't get it to roll.) 

[JarroVGIT]

Then the ALLEXCEPT() should work, that takes all the rows of the table except where tehre are filters on column xyz. In your case, we want ALLEXCEPT(Query1, Query1[SoldMonth]) in your FILTER statements in your CALCULATE statements. Can you try that?