topic Re: Inverse Aggregator in Quick Measures Gallery
https://community.powerbi.com/t5/Quick-Measures-Gallery/Inverse-Aggregator/m-p/1096778#M529
<P>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.<BR /><BR />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.</P>Tue, 19 May 2020 18:02:48 GMTtrejogabe2020-05-19T18:02:48ZInverse Aggregator
https://community.powerbi.com/t5/Quick-Measures-Gallery/Inverse-Aggregator/m-p/342266#M91
<P>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.</P>
<P> </P>
<P>This measure's inputs include an aggregation type, SUM, COUNT, etc. ([AGGREGATION]), a value column ([Value]) and a category column ([Category]).</P>
<P> </P>
<P>The generic pseudo-code is:</P>
<P><SPAN>Inverse [Aggregation] of [Value] = </SPAN></P>
<P><SPAN> IF(</SPAN><BR /><SPAN> ISFILTERED('Table'[Category]),</SPAN><BR /><SPAN> CALCULATE(</SPAN><BR /><SPAN> [AGGREGATION]('Table'[Value]),</SPAN><BR /><SPAN> EXCEPT(</SPAN><BR /><SPAN> ALL('Table'[Category]),</SPAN><BR /><SPAN> VALUES('Table'[Category])</SPAN><BR /><SPAN> )</SPAN><BR /><SPAN> ),</SPAN><BR /><SPAN> SUM('Table'[Value])</SPAN><BR /><SPAN> )</SPAN></P>
<P> </P>
<P>The sample code is:</P>
<P>InverseSum = IF(<BR /> ISFILTERED('InverseAggregator'[Category]),<BR /> CALCULATE(<BR /> SUM('InverseAggregator'[Value]),<BR /> EXCEPT(<BR /> ALL('InverseAggregator'[Category]),<BR /> VALUES('InverseAggregator'[Category])<BR /> )<BR /> ),<BR /> SUM('InverseAggregator'[Value])<BR /> )</P>
<P> </P>
<P><SPAN class="reportid hidden">eyJrIjoiZDdmMzUwNGMtMGQxZS00MDgwLWEyMzItODA1NDVhMmQzZGNiIiwidCI6IjRhMDQyNzQzLTM3M2EtNDNkMi04MjdiLTAwM2Y0YzdiYTFlNSIsImMiOjN9</SPAN></P>Wed, 27 Jun 2018 20:23:00 GMThttps://community.powerbi.com/t5/Quick-Measures-Gallery/Inverse-Aggregator/m-p/342266#M91Greg_Deckler2018-06-27T20:23:00ZRe: Inverse Aggregator
https://community.powerbi.com/t5/Quick-Measures-Gallery/Inverse-Aggregator/m-p/456733#M162
<P>Hi Greg,</P>
<P>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). </P>
<P> </P>
<P>Table name is 'Account'</P>
<P>Field name is [Name]</P>
<P> </P>
<P>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?</P>Fri, 06 Jul 2018 15:33:31 GMThttps://community.powerbi.com/t5/Quick-Measures-Gallery/Inverse-Aggregator/m-p/456733#M162eritter2018-07-06T15:33:31ZRe: Inverse Aggregator
https://community.powerbi.com/t5/Quick-Measures-Gallery/Inverse-Aggregator/m-p/456763#M163
<P>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. </P>
<P> </P>
<P>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. </P>
<P> </P>
<P>Other suggestions very welcome!</P>Fri, 06 Jul 2018 16:19:28 GMThttps://community.powerbi.com/t5/Quick-Measures-Gallery/Inverse-Aggregator/m-p/456763#M163eritter2018-07-06T16:19:28ZRe: Inverse Aggregator
https://community.powerbi.com/t5/Quick-Measures-Gallery/Inverse-Aggregator/m-p/457836#M165
<P>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?</P>Mon, 09 Jul 2018 12:57:08 GMThttps://community.powerbi.com/t5/Quick-Measures-Gallery/Inverse-Aggregator/m-p/457836#M165Greg_Deckler2018-07-09T12:57:08ZRe: Inverse Aggregator
https://community.powerbi.com/t5/Quick-Measures-Gallery/Inverse-Aggregator/m-p/457917#M166
<P>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. </P>Mon, 09 Jul 2018 13:59:23 GMThttps://community.powerbi.com/t5/Quick-Measures-Gallery/Inverse-Aggregator/m-p/457917#M166eritter2018-07-09T13:59:23ZRe: Inverse Aggregator
https://community.powerbi.com/t5/Quick-Measures-Gallery/Inverse-Aggregator/m-p/462131#M167
<P>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.</P>Fri, 13 Jul 2018 20:45:31 GMThttps://community.powerbi.com/t5/Quick-Measures-Gallery/Inverse-Aggregator/m-p/462131#M167eritter2018-07-13T20:45:31ZRe: Inverse Aggregator
https://community.powerbi.com/t5/Quick-Measures-Gallery/Inverse-Aggregator/m-p/1096778#M529
<P>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.<BR /><BR />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.</P>Tue, 19 May 2020 18:02:48 GMThttps://community.powerbi.com/t5/Quick-Measures-Gallery/Inverse-Aggregator/m-p/1096778#M529trejogabe2020-05-19T18:02:48ZRe: Inverse Aggregator
https://community.powerbi.com/t5/Quick-Measures-Gallery/Inverse-Aggregator/m-p/1096790#M530
<P><LI-USER uid="215888"></LI-USER> - Not entirely sure what you mean by grouping and that. Any chance you can share a PBIX or sample data/example? See this post <A href="https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490" target="_blank">https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490</A><BR /><BR />The most important parts are:<BR />1. Sample data as text, use the table tool in the editing bar<BR />2. Expected output from sample data<BR />3. Explanation in words of how to get from 1. to 2.</P>Tue, 19 May 2020 18:12:16 GMThttps://community.powerbi.com/t5/Quick-Measures-Gallery/Inverse-Aggregator/m-p/1096790#M530Greg_Deckler2020-05-19T18:12:16Z