Aggregate and ignore the selected dimension

[Anonymous]

Hi

I want to aggregate the data, but ignore the selected (User Clicked) dimension. How do I add the aggregateb expression/measure that would filter out the selected dimesion but add rest of them? 

[Greg_Deckler]

Perhaps grab ALL of them, EXCEPT out the chosen one and then make sure that the values you want are IN the resulting table. I built an Inverse Aggregator once: https://community.powerbi.com/t5/Quick-Measures-Gallery/Inverse-Aggregator/m-p/342266

[Anonymous]

Thanks Greg, It worked as expected for the measure. How do I show all other dimensions as well? If I filter the dimensions, the other dimensions are going away.

[v-juanli-msft · Accepted solution]

Hi @Anonymous 

Your requirment can be achieved.

Based on Greg_Deckler's pbix, i create a new table,

Table = InverseAggregator

don't connect this table to original table.

create a new measure in this new table

Measure =
IF (
    ISFILTERED ( InverseAggregator[Category] ),
    CALCULATE (
        SUM ( 'Table'[Value] ),
        EXCEPT ( VALUES ( 'Table'[Category] ), VALUES ( InverseAggregator[Category] ) )
    ),
    0
)

Best Regards

Maggie