Filter and exclude the data

[Anonymous]

Hi experts,

I have a task don't know how to solve and would like to have your suggestion.

I have a list of data including 'member ID', 'invoice amount', 'type of stores'.

I want to see how much money the customers who've purchased 'cosmetics' (which is a 'type of stores') have also spent in other 'type of stores'. e.g. restaurant/supermarket...etc

I expect the x-axis will be the 'type of stores' and the y-axis will be 'invoice amount' but only with the customer who've purchased cosmetics will be taken into account. How should I do this?

The challenge here is how to exclude the 'invoice amount' by 'cosmetics' but still use 'cosmetics' as a filter to select the 'member ID'

Any help? 

[v-jiascu-msft · Accepted solution]

Hi @Anonymous,

It seems a common requirement. Please download the demo in the attachment. It's better to have your data sample. The result of the data below should be 60.

member ID invoice amount type of stores

1	10	cosmetics
1	20	restaurant
2	30	cosmetics
2	40	restaurant
3	50	cosmetics
4	60	restaurant
5	70	cosmetics

Measure =
VAR qulifiedCust =
    CALCULATETABLE (
        VALUES ( Table1[member ID] ),
        FILTER ( ALL ( Table1 ), 'Table1'[type of stores] = "cosmetics" )
    )
RETURN
    SUMX ( 'Table1', IF ( [member ID] IN qulifiedCust, [invoice amount], 0 ) )

Best Regards,
Dale

[Anonymous]

Hi Dale,

Many thanks for the help.

It has solved my problem.

Didn't upload my data sample as the sample is a bit big (5000+rows and 10+ columns)

A follow-up question, what if the question is: all the transaction record with members who haven't purchased cosmetics?

[v-jiascu-msft]

Hi @Anonymous,

Can it help by switching the parameter? 

Measure 2 =
VAR qulifiedCust =
    CALCULATETABLE (
        VALUES ( Table1[member ID] ),
        FILTER ( ALL ( Table1 ), 'Table1'[type of stores] = "cosmetics" )
    )
RETURN
    SUMX ( 'Table1', IF ( [member ID] IN qulifiedCust, 0, [invoice amount] ) )

or this one?

Measure 3  = 
VAR qulifiedCust =
    CALCULATETABLE (
        VALUES ( Table1[member ID] ),
        FILTER ( ALL ( Table1 ), 'Table1'[type of stores] = "cosmetics" )
    )
RETURN
    SUMX ( 'Table1', IF ( [member ID] IN qulifiedCust && [type of stores] <> "cosmetics", 0, [invoice amount] ) 
)

Best Regards,
Dale

[Anonymous]

Hi Dale,

I got the result from Measure 2.  The result of Measure 3 will still have cosmetics.

I believe it has solved my problem.

Thank you very much!

[Greg_Deckler]

Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

That being said, not positive but my Inverse Aggregator might help:

https://community.powerbi.com/t5/Quick-Measures-Gallery/Inverse-Aggregator/m-p/342266