The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event: Join us in Stockholm, September 24-27, 2024.

**Save €200 with code MSCUST on top of early bird pricing!**

- Power BI forums
- Updates
- News & Announcements
- Get Help with Power BI
- Desktop
- Service
- Report Server
- Power Query
- Mobile Apps
- Developer
- DAX Commands and Tips
- Custom Visuals Development Discussion
- Health and Life Sciences
- Power BI Spanish forums
- Translated Spanish Desktop
- Training and Consulting
- Instructor Led Training
- Dashboard in a Day for Women, by Women
- Galleries
- Community Connections & How-To Videos
- COVID-19 Data Stories Gallery
- Themes Gallery
- Data Stories Gallery
- R Script Showcase
- Webinars and Video Gallery
- Quick Measures Gallery
- 2021 MSBizAppsSummit Gallery
- 2020 MSBizAppsSummit Gallery
- 2019 MSBizAppsSummit Gallery
- Events
- Ideas
- Custom Visuals Ideas
- Issues
- Issues
- Events
- Upcoming Events
- Community Blog
- Power BI Community Blog
- Custom Visuals Community Blog
- Power BI 中文博客
- Community Support
- Community Accounts & Registration
- Using the Community
- Community Feedback

Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

- Power BI forums
- Forums
- Get Help with Power BI
- Desktop
- Re: Rolling sums, but on a text field...

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Rolling sums, but on a text field...

12-26-2019
09:14 AM

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

Solved! Go to Solution.

2 ACCEPTED SOLUTIONS

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

01-02-2020
12:40 PM

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! 🙂

Proud to be a Super User!

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

01-03-2020
01:26 AM

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! 🙂

Proud to be a Super User!

24 REPLIES 24

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

12-26-2019
07:22 PM

Hi,

Share some data and show the expected result.

Regards,

Ashish Mathur

http://www.ashishmathur.com

https://www.linkedin.com/in/excelenthusiasts/

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

12-27-2019
07:21 AM

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

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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

12-26-2019
12:03 PM

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! 🙂

Proud to be a Super User!

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

12-26-2019
02:05 PM

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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

12-26-2019
03:23 PM

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.

**Did I answer your question? Mark my post as a solution!**

Proud to be a Super User!

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

12-27-2019
07:00 AM

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!

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

12-27-2019
08:58 AM

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

**Did I answer your question? Mark my post as a solution!**

Proud to be a Super User!

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

12-27-2019
10:42 AM

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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

12-27-2019
10:46 AM

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.

Proud to be a Super User!

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

01-02-2020
12:40 PM

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

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

**mark it as the Solution**. This also helps others to find what they are looking for.

Keep those **thumbs up** coming! 🙂

Proud to be a Super User!

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

01-02-2020
02:12 PM

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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

01-02-2020
02:44 PM

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

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

**mark it as the Solution**. This also helps others to find what they are looking for.

Keep those **thumbs up** coming! 🙂

Proud to be a Super User!

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

01-02-2020
03:36 PM

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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

01-03-2020
01:26 AM

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

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

**mark it as the Solution**. This also helps others to find what they are looking for.

Keep those **thumbs up** coming! 🙂

Proud to be a Super User!

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

01-03-2020
06:40 AM

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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

01-02-2020
12:45 PM

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

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

**mark it as the Solution**. This also helps others to find what they are looking for.

Keep those **thumbs up** coming! 🙂

Proud to be a Super User!

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

12-27-2019
11:20 AM

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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

12-27-2019
11:54 AM

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! 😄

Proud to be a Super User!

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

12-27-2019
02:00 PM

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

12-27-2019
02:38 PM

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?

Proud to be a Super User!

Announcements

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Check out the August 2024 Power BI update to learn about new features.

Learn from experts, get hands-on experience, and win awesome prizes.

Featured Topics

Top Solution Authors

User | Count |
---|---|

113 | |

80 | |

77 | |

43 | |

39 |

Top Kudoed Authors

User | Count |
---|---|

150 | |

117 | |

67 | |

64 | |

56 |