Modify measures depending on filters

[mizaskun]

Good morning,

I have a very simple model:

User	Type	Amount
A	Z	100
A	Z	200
A	Y	50

I created the following measure:

TotalByUser = calculate( sum(Table[Amount]), allexcept(Table, Table[User]))

I created a very simple report:

User	Type	Amount	TotalByUser
A	Z	300	350
A	Y	50	350

And when I filter the report by Type = Z, y get the following:

User	Type	Amount	TotalByUser
A	Z	300	350

What I would need is to filter the measure the same way the report is being filtered. The result should be:

User	Type	Amount	TotalByUser
A	Z	300	300

And if I unresume the Amount field, the result should look like this applying the filter:

User	Type	Amount	TotalByUser
A	Z	100	300
A	Z	200	300

And like this if I do not apply it:

User	Type	Amount	TotalByUser
A	Z	100	350
A	Z	200	350
A	Y	50	350

Is it possible to achieve this? Does anybody know how to do it? 

Thank you so much!

[daxer-almighty]

@mizaskun 

If your model is much different from what you show here, then it'll be very hard to give you a good solution.

But even if you do show the right model, I'd recommend you first read this: Using ALLEXCEPT versus ALL and VALUES - SQLBI

It may explain why you see what you see.

[mizaskun]

Thank you so much. I will. 

[amitchandak]

@mizaskun , Try a measure like

sumx(values(Table[User]) ,if(isfiltered(Table[Type]) , calculate(sum(Table[Amount])), calculate( sum(Table[Amount]), allexcept(Table, Table[User]))))

[mizaskun]

Good morning,

Thank you for your answer! The model actually have much more fields through which can be filtered. The table I explained was a simplification to make it easier to understand what I need. There should be a way to achieve that behavour. 

Thank you so much! 

[daxer-almighty · Accepted solution]

TotalByUser =
CALCULATE(
    SUM( Table[Amount] ),
    VALUES( Table[User] ),
    ALLSELECTED( 'Table' )
)

It might be that the above does what you want...