Inverse Aggregator 07-09-2018 06:59 AM

[Greg_Deckler]

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

[Anonymous]

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.

[Greg_Deckler]

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

[Anonymous]

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!

[Greg_Deckler]

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?

[Anonymous]

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.

[Anonymous]

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. 

[Anonymous]

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?