DAX Query to filter correctly

[Anonymous]

 Hi all, 

I have a table smilar to the attached snapshot. I need to create a metric that shows sum of market transactions (for a selected store) when I select a store in the slicer. 

My problem is that when I select a store, Market-sum filetrs shows value of that store and not the entire market of that store. 

For example if I select store 21, answer should be 1829 and not 604. 

Thanks!

[Anonymous]

Since store 21, belongs to Market C. Answer should be sum of Transactions of Market C: 1829

[vivran22 · Accepted solution]

@Anonymous

Try this:

Market Sum =
CALCULATE (
    SUM ( 'Table'[Transaction] ),
    FILTER ( ALL ( 'Table' ), 'Table'[Market] = SELECTEDVALUE ( 'Table'[Market] ) )
)

Bless you!
Vivek

If it helps, please mark it as a solution
Congratulations would be a cherry on top 🙂

https://www.vivran.in/

Connect on LinkedIn

[amitchandak]

@Anonymous 

I think your data is not matching what you set. Filter 21 seems to be working correctly. Do you want sum of market or State?

[vivran22]

Hello @Anonymous ,

Please share sample data file/more details around the existing data. The issue is not clear from the description.

Cheers!
Vivek

https://www.vivran.in/
Connect on LinkedIn