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
- Calculating SUM of TOPN rows

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
- Email to a Friend
- Report Inappropriate Content

Calculating SUM of TOPN rows

08-17-2016
08:43 AM

Hello everyone! I am trying to compute SUM of TOP N values in a table in a time period specified via slicers using following measures:

1. VolumeSum = CALCULATE(SUM(Volumes[Volume]); Volumes[Dimension]="mln l")

2. VolumeRanks2 = RANKX(ALL(Producers[Producer Eng]); CALCULATE(SUM(Volumes[Volume]); Volumes[Dimension]="mln l"); ;DESC;Dense)

3. Selected TopN = MIN('TopN'[TopN])

4. TopVolume = CALCULATE([VolumeSum]; FILTER(Volumes; [VolumeRanks2]<=[Selected TopN]))

When I try to add a card with a TopVolume measure it gives me the following result:

Note: there is a visual level filter on chart to show only [Selected TOPN] values.

As you can see, it sums volumes of every producer, not the ones with rank <= 3. I've already tried different approaches (calculated columns, TOPN function etc), however, I was unable to get desired result.

Here is my data model:

Any help is appreciated and thank you all in advance.

Best Regards,

Bogdan

Solved! Go to Solution.

1 ACCEPTED SOLUTION

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

08-17-2016
12:31 PM

Hi @BogdanP. Here's how I modified your TopVolume formula to get it to work with some made up data:

TopVolume = CALCULATE(

SUMX(VALUES(Producers[Producer Eng]), [VolumeSum]),

FILTER(ALL(Producers[Producer Eng]), [VolumeRanks2]<=[Selected TopN])

)

And a screenshot of it in action:

Hopefully this works for your model. If not, please include some sample data. I know enough about DAX to figure it out, but it takes some puzzling first. I'll give an explanation a shot, and a more seasoned pro can correct or add more if needed 🙂

As it was, there was nothing in the formula to summarize the values at the Producer Eng level, which made the rank of no real effect within the formula. Essentially, you got the sum of Volumes for everyone, because every row was evaluated against itself and got a rank of 1.

Adding in the SUMX tells the formula that we want to first get the VolumeSum amounts for each Producer Eng, then rank them at that level and determine if the rank is less than or equal to the Selected TopN.

This article is where I got my start with SUMX. It's a bit dated, but I still refer to it for a simple breakdown.

You'll also notice I changed the FILTER context from using the Volumes table to the ALL(Producers[Producers Eng]) column. Because RANKX is working on that column, it's what we need to include all of for the RANKX to function like we want.

5 REPLIES 5

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

08-17-2016
12:31 PM

Hi @BogdanP. Here's how I modified your TopVolume formula to get it to work with some made up data:

TopVolume = CALCULATE(

SUMX(VALUES(Producers[Producer Eng]), [VolumeSum]),

FILTER(ALL(Producers[Producer Eng]), [VolumeRanks2]<=[Selected TopN])

)

And a screenshot of it in action:

Hopefully this works for your model. If not, please include some sample data. I know enough about DAX to figure it out, but it takes some puzzling first. I'll give an explanation a shot, and a more seasoned pro can correct or add more if needed 🙂

As it was, there was nothing in the formula to summarize the values at the Producer Eng level, which made the rank of no real effect within the formula. Essentially, you got the sum of Volumes for everyone, because every row was evaluated against itself and got a rank of 1.

Adding in the SUMX tells the formula that we want to first get the VolumeSum amounts for each Producer Eng, then rank them at that level and determine if the rank is less than or equal to the Selected TopN.

This article is where I got my start with SUMX. It's a bit dated, but I still refer to it for a simple breakdown.

You'll also notice I changed the FILTER context from using the Volumes table to the ALL(Producers[Producers Eng]) column. Because RANKX is working on that column, it's what we need to include all of for the RANKX to function like we want.

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

07-21-2017
09:18 AM

Hi @KGrice,

I'm also having a challenge with getting the sum of a rank. I've tried your solution, but something in my data model won't correctly compute the solution. I have several fitlers applied to my page, so I'm guessing that has something to do with it, but curious if may have any insight.

Here's my ranking formula:

As you can see, the ranking works correctly, showing the correct Qty totals based on my page filters. However, my aggregation formula result is over 25,000. Clearly, not correct. Any ideas?

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

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

08-17-2016
12:49 PM

Thank you @KGrice so much for such a detailed answer! I suspected that the problem lies exactly in evaluating rows all by itself, however, i went wrong way trying to use SUMMARIZE by [Producer Eng]. I've even tried SUMX but never used VALUES.

I am quite new to DAX and PowerBI and I will definitely dig more into basics like SUMX, AVERAGEX etc to avoid bothering people with dumb questions:)

Thank you once again!

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

08-17-2016
02:32 PM

You're welcome @BogdanP! And keep posting the "dumb" questions, since tyring to figure them out is the only way I learn. Not sure what that says about me.

Announcements

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Featured Topics

Top Solution Authors

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

431 | |

147 | |

119 | |

51 | |

49 |

Top Kudoed Authors

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

451 | |

132 | |

126 | |

81 | |

73 |