Values of all related clients based on product filter

[ImreP]

Hi,

I have a requirement where I need to get the values of all products of all clients that has a product a user filters.

Here is the example:

Product	Client	Volume	Expected volume
C	1	121	x
M	1	1000	x
D	1	200	x
C	2	112	x
D	2	300	x
M	3	2000
D	4	400	x
C	4	154	x
		4287	2287

Let's say the user slices by Product 'C'. In this case the required Measure result is the sum of all products where any clients has product 'C'. Then 'Expected volume' marks which volumes should be included in the result in case of product 'C' filter.

In SQL I'd do the following:

select
sum(Volume)
from table

where client in

(select
client
from table
where product='C')

But I need this to be dynamic therefore I think it should be written in a measure. 

Could you please help me with this?

Thanks.

[Greg_Deckler]

@ImreP Try:

Measure =
  VAR __Clients = DISTINCT('Table'[Client])
  VAR __Table = FILTER(ALL('Table'),[Client] IN __Clients)
RETURN
  SUMX(__Table,[Volume])

[ImreP]

It's not fast. Actually it does not give a result after minutes. I was out of memory after about 7 minutes. 

I guess it is expected to be slow. 

Are there any options that are less resource intensive? It could be tables, measures. The data model is much more complex than my example and there are multiple filters in the user's hand. All other filters should work as "normal". But testing on my sample table, your formula does just that, so I would think your formula would result in the desired output. 

@Greg_Deckler 

[Greg_Deckler]

@ImreP You can try something like this:

Measure =
SUMX(
	KEEPFILTERS(DISTINCT('Table'[Client])),
	CALCULATE(SUM('Table'[Volume]))
)

[ImreP]

@Greg_Deckler The performance is okay but the result is wrong. Only the chosen product is taken into account on client level. 

[Greg_Deckler]

@ImreP Yeah, the joys of CALCULATE, maybe this:

Measure =
SUMX(
	KEEPFILTERS(DISTINCT('Table'[Client])),
	CALCULATE(SUM('Table'[Volume]),ALL('Table'))
)

[ImreP]

Thanks! Looks good on the example. I'm going to try it on the actual data model.