Filtering on summarised data

[Anonymous]

Hi,

I have a table that has the following headings:

Broker     USD Spend   

XXX         $100

YYY         $200

Each row has a couple of columns that categorise by region: EMEA, APAC, CEE for example. I have made a filter for all of those columns using: https://community.powerbi.com/t5/Desktop/Once-Slicer-for-multiple-columns/td-p/407949

So my slicer now has all the options (EMEA, APAC, CEE).

As USD spend is a summarised column, when I change the region, the USD spend does not update to show how much each broker's usd spend is. For example, if broker XXX has two orders of $20 that are both from EMEA, and we selected 'EMEA' from the slicer, it still shows $100 in the broker table. 

Is there anyway to solve this issue without making USD spend unsummarised? This doesn't look great compared to summarised but I would like the filtering to work correctly. 

[Anonymous · Accepted solution]

Fixed this with a calculate dax function:

IF(ISFILTERED('SlicerTable for CCY region'[Region]),
SWITCH(TRUE(),
selectedFilter="EMEA",CALCULATE(SUM('Table 1'[USD Spend]),FILTER('Table1','Table1'[EMEA]=selectedFilter)),

etc

[amitchandak]

Refer

https://community.powerbi.com/t5/Desktop/Creating-a-dynamic-summarized-table-or-calculated-table/td-...

https://community.powerbi.com/t5/Desktop/DAX-Dynamic-Table-and-Summarize-Table/td-p/304105

The ideal way is that you use unsummarized data.

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...
https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p...
https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601