Turn on suggestions

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

Showing results for

- Microsoft Power BI Community
- Forums
- Get Help with Power BI
- Desktop
- Re: Cumulative sum of a measure which calculates a...

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

Highlighted
##

matthewsignal

Frequent Visitor

Cumulative sum of a measure which calculates a cumulative sum

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

04-12-2018
05:12 PM

Hi there,

I am a new user of Power BI and I am trying to learn about measures and DAX etc

I am trying to create a cumulative sum of a measure that itself calculates a cumulative sum (all of which needs to be sliceable).

A table view of the data/measures I have created is follows:

Where:

Age column is pulled in straight from a Table.

Total Assets is a simple measure: Total Assets = COUNT(Assets[Age]) + 0

Cumulative Assets was generated using the quick measure running total:

Cumulative Assets =

CALCULATE(

[Total Assets],

FILTER(

ALLSELECTED('Age Table'[Age]),

ISONORAFTER('Age Table'[Age], MAX('Age Table'[Age]), ASC)

)

)

Total Faults is a simple measure: Total Faults = COUNT(Faults[Age at Fault]) + 0

Fault Rate is a simple measure: Fault Rate = DIVIDE([Total Faults],[Cumulative Assets]) + 0

The desired goal is to have "Cumulative Fault Rate" calculate a running total of the Fault Rate as a function of age.

The column should contain [0.27, 0.47, 0.87, 0.87, 0.87...]

I have tried to implement using the quick measure running total:

Cumulative Fault Rate =

CALCULATE(

[Fault Rate],

FILTER(

ALLSELECTED('Age Table'[Age]),

ISONORAFTER('Age Table'[Age], MAX('Age Table'[Age]), DESC)

)

)

As the table shows, the "Cumulative Fault Rate" column is not showing what I want....

Finally, I would like the measures to be dynamic with the model slicer (I am assuming that is where the "ALLSELECTED" function fits in....)

Apologies if this has been covered, I did read a couple of other threads and tried to solve on my own...but couldn't get it working.

Thanks,

Matt

7 REPLIES 7

Highlighted
##

v-xjiin-msft

Solution Sage

Re: Cumulative sum of a measure which calculates a cumulative sum

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

04-13-2018
02:41 AM

Hi @matthewsignal,

To make a cumulative sum on a measure, you can try following measure:

Cumulative Fault Rate = SUMX ( FILTER ( ALLSELECTED ( 'Age Table'[Age] ), 'Age Table'[Age] <= MAX ( 'Age Table'[Age] ) ), [Fault Rate] )

Thanks,

Xi Jin.

Highlighted
##

matthewsignal

Frequent Visitor

Re: Cumulative sum of a measure which calculates a cumulative sum

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

04-15-2018
01:14 PM

Hi @v-xjiin-msft,

thanks for your fast response.

Unfortunately that hasn't worked for me, see result below .

The result has changed compared to my first attempt, but still not correct.

Did you enter the fault rate data in a table or calcuate it via the equations I posted originally when developing your solution?

I am keen to hear any further ideas anyone has on how to solve this.

Many thanks,

Matt

Highlighted
##

v-xjiin-msft

Solution Sage

Re: Cumulative sum of a measure which calculates a cumulative sum

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

04-15-2018
07:18 PM

Hi @matthewsignal,

Since I can't see your real data and you have only shared some measure results. It is hard for me to repro your issue. Could you please share us your pbix file with One Drive or Google Drive if possible? So that I can get a right direction.

If you can't, please share some sample data which I can copy and paste directly.

Thanks,

Xi Jin.

Highlighted
##

matthewsignal

Frequent Visitor

Re: Cumulative sum of a measure which calculates a cumulative sum

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

04-15-2018
07:40 PM

Hi @v-xjiin-msft,

Yes sure, I have uploaded the pbix file to dropbox, see the link:

https://www.dropbox.com/s/sgpxnx1buf28e7h/cumul%20sum%20data.pbix?dl=0

It is a very basic data set to illistrate the challenge I am having.

Let me know if you have any questions.

thanks,

Matt

Highlighted
##

v-xjiin-msft

Solution Sage

Re: Cumulative sum of a measure which calculates a cumulative sum

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

04-16-2018
12:06 AM

Hi @matthewsignal,

OK. I got it. The issue is on the Cumulative Assets formula. The order of this cumulative is from bigger Age to smaller Age as when Age is 5 Cumulative Assets is 3 and when Age is 0 Cumulative Assets is 11. Which should be wrong in your scenario. To modify your issue, you can simply change ASC to DESC, then use my formula to get the Cumulative Fault Rate.

Cumulative Assets = CALCULATE( [Total Assets], FILTER( ALLSELECTED('Age Table'[Age]), ISONORAFTER('Age Table'[Age], MAX('Age Table'[Age]), DESC) ) )

By the way, if you want to get a cumulative sum of a measure. I would highly suggest you to use SUMX() instead of using the quick measure. For the Cumulative Assets, it can be:

Cumulative Assets = SUMX( FILTER( ALLSELECTED('Age Table'[Age]), 'Age Table'[Age] <= MAX ( 'Age Table'[Age] ) ), [Total Assets] )

Thanks,

Xi Jin.

Highlighted
##

matthewsignal

Frequent Visitor

Re: Cumulative sum of a measure which calculates a cumulative sum

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

04-16-2018
01:54 AM

Hi @v-xjiin-msft,

glad to hear you got the file OK.

I want the cumulative asset formula to be desc.

think of it as 'total number of assets >= age(row)'....every month the current assets move into the next age bracket higher

Is it possible to have the cumulative asset formula desc like it currently is, and the cumulative fault rate increasing with age (eg asc)?

Thanks for your help on this

Matt

Highlighted
##

matthewsignal

Frequent Visitor

Re: Cumulative sum of a measure which calculates a cumulative sum

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

04-25-2018
01:26 PM

Hi @v-xjiin-msft,

Did my last reply make sense?

I have still not been able to solve this issue and was wondering if you had any further ideas?

I appreciate your time and help with this.

thanks,

Matt

Announcements

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

Who are our Super User Superstars? Who made it to the top of the leaderboards? Get the answers!

Visit our Community Blog for articles, guides, and information created by fellow community members.

Top Kudoed Authors

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

445 | |

199 | |

130 | |

127 | |

94 |