Calculating the remainder

Anything related to PowerPivot and DAX Formuale
Posts: 52
Joined: Sun Jul 31, 2016 2:29 pm

Calculating the remainder

Postby Steve_D » Fri Sep 01, 2017 9:34 am

I had a business problem where I filtered a data set with multiple customers which was related to a sales table to show the sales to a customer that was selected. Pretty normal but, then I wanted to know what the sales where for the remaining customers... all others with the exception of those I had selected (the remainder).

My solution was to calculate the Total (ie. Calculate(Sum(Table.[Sales]), All(customers)) and subtract the filtered value (Sum(Table.[Sales]). This works but, the method doesn't seem efficient to me and I was wondering if there is another way of doing it. ie a DAX function that removes the filter for all except those selected.

Posts: 887
Joined: Sun May 04, 2014 4:01 pm
Location: Sydney, Australia

Re: Calculating the remainder

Postby MattAllington » Sat Sep 02, 2017 5:44 am

I think it is fine and i am not aware of a better way. Power Pivot is very fast at filtering and adding columns, and adding an unfiltered copy (e.g. Your all version) is probably the fastest of all
Matt Allington is Professional Self Service BI Consultant, Trainer and Author of the book "Learn to Write DAX". You can hire me at

Return to “PowerPivot/DAX”

Who is online

Users browsing this forum: No registered users and 2 guests