skip to main content
Power BI
    • What is Power BI
    • Why Power BI
    • Customer stories
    • Data visuals
    • Security
    • Power BI Desktop
    • Power BI Pro
    • Power BI Premium
    • Power BI Mobile
    • Power BI Embedded
    • Power BI Report Server
  • Pricing
    • Azure + Power BI
    • Office 365 + Power BI
      • Energy
      • Healthcare
      • Manufacturing
      • Media
      • Retail
    • For analysts
    • For IT
      • Overview
      • Embedded analytics
      • Power BI visuals
      • Automation
      • Documentation
      • Community
    • Overview
    • Find consulting services
    • Partner showcase
    • Find a partner
    • Become a partner
    • Instructor-led training
    • Getting started
      • Overview
      • Online workshops
      • Self-guided learning
      • Webinars
      • Documentation
      • Roadmap
      • Overview
      • Issues
      • Give feedback
    • Blog
    • Business intelligence topics
    • Overview
    • Forums
    • Galleries
    • Submit ideas
    • Events
    • User groups
    • Community blog
    • Register
    • ·
    • Sign in
    • ·
    • Help
    Go To
    • 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
    cancel
    Turn on suggestions
    Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
    Showing results for 
    Search instead for 
    Did you mean: 
    • Microsoft Power BI Community
    • Galleries
    • Quick Measures Gallery
    • Inverse Aggregator

    Inverse Aggregator

    01-21-2018 10:42 AM - last edited 06-27-2018 13:23 PM

    Super User Greg_Deckler
    Super User
    8343 Views
    LinkedIn LinkedIn Facebook Facebook Twitter Twitter
    Greg_Deckler
    Super User Greg_Deckler
    Super User
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Email to a Friend
    • Report Inappropriate Content

    Inverse Aggregator

    ‎01-21-2018 10:42 AM

    This measure computes the aggregation of the specified value but inverts a slicer selection on the specified category. In other words, instead of returning the SUM, COUNT or other aggregation of what is selected in a slicer, this measure computes the inverse of that selection such that selected items are left out of the calculation. Thus, if no items are selected, the measure returns the aggregation of all items. If all items are selected, then the measure returns blank.

     

    This measure's inputs include an aggregation type, SUM, COUNT, etc. ([AGGREGATION]), a value column ([Value]) and a category column ([Category]).

     

    The generic pseudo-code is:

    Inverse [Aggregation] of [Value] =

                            IF(
                                ISFILTERED('Table'[Category]),
                                CALCULATE(
                                                    [AGGREGATION]('Table'[Value]),
                                                    EXCEPT(
                                                                 ALL('Table'[Category]),
                                                                 VALUES('Table'[Category])
                                                     )
                                ),
                                SUM('Table'[Value])
                            )

     

    The sample code is:

    InverseSum = IF(
                                ISFILTERED('InverseAggregator'[Category]),
                                CALCULATE(
                                                    SUM('InverseAggregator'[Value]),
                                                    EXCEPT(
                                                                 ALL('InverseAggregator'[Category]),
                                                                 VALUES('InverseAggregator'[Category])
                                                     )
                                ),
                                SUM('InverseAggregator'[Value])
                            )

     

    eyJrIjoiZDdmMzUwNGMtMGQxZS00MDgwLWEyMzItODA1NDVhMmQzZGNiIiwidCI6IjRhMDQyNzQzLTM3M2EtNDNkMi04MjdiLTAwM2Y0YzdiYTFlNSIsImMiOjN9


    @ me in replies or I'll lose your thread!!!
    Become an expert!: Enterprise DNA
    External Tools: MSHGQM
    YouTube Channel!: Microsoft Hates Greg
    Latest book!:
    Mastering Power BI 2nd Edition
    inverseaggregator.pbix
    23 KB
    Labels:
    • Labels:
    • Filters
    • Mathematical
    • Totals
    Message 1 of 8
    8,343 Views
    2
    Reply
    • All forum topics
    • Previous Topic
    • Next Topic
    Anonymous
    Not applicable
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Email to a Friend
    • Report Inappropriate Content

    ‎05-19-2020 11:02 AM

    Has anyone found a better solution than the grouping? This is exactly my scenario as well. Thus, everyone's solutions using DAX aren't very feasible because I too have numerous visuals and measures so I would need to modify the DAX everywhere which I think is too cumbersome of a solution to do some filtering. I literally just need a slicer that does what an admin filter on the right does which is allow you to select all but one particular value as a way to exlude that value.

    What I have done is just add a regular slicer with a 'Select All' option and placed a filter on that slicer to only display the Account/Company that I know my users want to exclude. They will need to first click the 'Select All' option in this slicer and then deselect the Account in order to exlude it from all the visuals. This is also not the most sophisticated solution but it works.

    Message 7 of 8
    5,358 Views
    0
    Reply
    Greg_Deckler
    Super User Greg_Deckler
    Super User
    In response to Anonymous
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Email to a Friend
    • Report Inappropriate Content

    ‎05-19-2020 11:12 AM

    @Anonymous - Not entirely sure what you mean by grouping and that. Any chance you can share a PBIX or sample data/example? See this post https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

    The most important parts are:
    1. Sample data as text, use the table tool in the editing bar
    2. Expected output from sample data
    3. Explanation in words of how to get from 1. to 2.


    @ me in replies or I'll lose your thread!!!
    Become an expert!: Enterprise DNA
    External Tools: MSHGQM
    YouTube Channel!: Microsoft Hates Greg
    Latest book!:
    Mastering Power BI 2nd Edition
    Message 8 of 8
    5,356 Views
    0
    Reply
    Anonymous
    Not applicable
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Email to a Friend
    • Report Inappropriate Content

    ‎07-06-2018 09:19 AM

    I found a way to do this - not really visually pleasing, but it works.  I used a Slicer as the visual, then created a group of my 'Account'[Name] field.  I grouped all the existing names except the one customer I want to toggle.  Now I'm presented with a visual that inlcudes the grouped values (All customers but the one) and the Ungrouped values (one customer).  I have 2 check boxes, when both are checked, every customer is show in the data, when I remove the check on the one customer (ungrouped value) it just removes the data for that one customer.  Works, not elegant. 

     

    I expect when we get a new customer, I will need to update group so that name is included in the Groups and members area.  This feels like a sledge hammer approach to putting in a tack, but it works. 

     

    Other suggestions very welcome!

    Message 3 of 8
    8,045 Views
    0
    Reply
    Greg_Deckler
    Super User Greg_Deckler
    Super User
    In response to Anonymous
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Email to a Friend
    • Report Inappropriate Content

    ‎07-09-2018 05:57 AM

    So, the real issue you are having sounds like that you want to see a table with the non-selected names (categories in the inverse aggregator example). In other words, unlike the inverse aggregator which shows a table of just the selected values, you want to show a table of the non-selected values from the slicer. Correct?


    @ me in replies or I'll lose your thread!!!
    Become an expert!: Enterprise DNA
    External Tools: MSHGQM
    YouTube Channel!: Microsoft Hates Greg
    Latest book!:
    Mastering Power BI 2nd Edition
    Message 4 of 8
    8,035 Views
    0
    Reply
    Anonymous
    Not applicable
    In response to Greg_Deckler
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Email to a Friend
    • Report Inappropriate Content

    ‎07-13-2018 01:45 PM

    The ugly part with my solution, if new clients are added, I need to regenerate the groups.  If there is another way to make this dynamic as new clients get added, that would be awesome.

    Message 6 of 8
    8,011 Views
    0
    Reply
    Anonymous
    Not applicable
    In response to Greg_Deckler
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Email to a Friend
    • Report Inappropriate Content

    ‎07-09-2018 06:59 AM

    That sounds correct.   Ideally, it would be awesome if there was a Visualization that allowed for complex filters to be applied so these types of actions would be easy to build. 

    Message 5 of 8
    8,033 Views
    0
    Reply
    Anonymous
    Not applicable
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Email to a Friend
    • Report Inappropriate Content

    ‎07-06-2018 08:33 AM

    Hi Greg,

    Thank you for the quick reply.  If I understand this correctly and after downloaded the sample, this impacts a second visual, ie the InverseSum card you created.  What I'm trying to do is impact all the existing visuals.  So, I have several visual tables, graphs, and cards.  If I'm in the PowerBI app, I can apply a filter of "Does not contain" and "CustomerName" and all metrics with "CustomerName" are removed from my current visuals.  I haven't seen a way to create a simple button or text input that would reference the table and field, apply the does not contain filter, and allow someone using the web power bi dashboard to remove the one customer either automatically (I code the name) or manually (they type the name). 

     

    Table name is 'Account'

    Field name is [Name]

     

    The idea is to use this as a toggle to show metrics with the one customer, and a simple toggle to show the metrics without the customer.  Does this explain my use case better?

    Message 2 of 8
    8,046 Views
    0
    Reply

    Power Platform

    • Overview
    • Power BI
    • Power Apps
    • Power Automate
    • Power Virtual Agents

    • Sign in
    • Sign up

    Browse

    • Solutions
    • Partners
    • Consulting Services

    Downloads

    • Power BI Desktop
    • Power BI Mobile
    • Power BI Report Server
    • See all downloads

    Learn

    • Guided learning
    • Documentation
    • Support
    • Community
    • Give feedback
    • Webinars
    • Developers
    • Blog
    • Newsletter

    © 2022 Microsoft

    Follow Power BI

    • Privacy & cookies
    • Manage cookies
    • Terms of use
    • Trademarks