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

[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

[eritter]

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?

[eritter]

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?

[eritter]

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. 

[eritter]

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.