Measure which shows only values larger than a certain amount of another measure

[kaniggit]

I have a measure which adds values from related tables Table A and Table B. 

Currently it just reads Measure1 = sum(TableA[value]) + sum(TableB[value])

This measure works great.

Now, how do I create a measure for just where "Measure 1 > 100"?  I have played around with sumx and calculate to no avail. I have changed Measure1 to use "sumx" function instead of sum.  

Please help!  Thank you.

[Ashish_Mathur]

Hi,

It will be helpful if you can show the data and the expected result.

[kaniggit]

Imagine you have 2 kinds of depositsin a financial institution:  Shares and Certificates. They each are in different tables, both tables containing account numbers.

One measure is:  Total_Deposits = sum(CERT[CECBAL])+sum(SHAR[SHCBAL])

What I need/would like is to create a "Large_Deposits" measure, that is:  "Total Deposits > 100000"

schema

[Ashish_Mathur]

Hi,

Try this

=CALCULATE([Total_Deposits],FILTER([Total_deposits],[Total_deposits]>100000))

Hope this helps.

[kaniggit]

I get this error message: "the FILTER function expects a table expression for argument '1', but a string or numeric expression was used."

[Ashish_Mathur]

Hi,

Sorry, try this

=CALCULATE([Total_Deposits],FILTER(Data,[Total_deposits]>100000))

[kaniggit]

Yes, this is the formula but the "data" part is the essence of my question I think... Since "Total_Deposits" comes from two separate tables, how do I "filter" BOTH tables and get the results from BOTH.  Something with "relatedtable" maybe?

TotalDeposits = sum(SHAR[Share_Balance]) + sum(CERT[Share_Balance]).  So when I FILTER, how do I still get the results from BOTH tables?  

[Ashish_Mathur]

Hi,

I dont think that should matter.  Do you get an incorrect result?

[kaniggit]

That solved the problem of pulling those together!  Even though I just used one table in the "filter" it seemed to work.   (Hooray for relationships!)

So here's what I did:   LargeDeposits = calculate([Total_Deposits],filter(SHAR,[Total_Deposits]>100000))

Okay, one other small problem:  The same account can have multiple rows in the SHAR table. (For example, you could have a Money Market and a Checking).   For "large deposits," it is filtering on a row-by-row basis, so if you have a $150,000 Money Market that is being included, but it would exclude your $30,000 checking suffix.

How can I tweak my filter to include ALL SHARes for that account if the Total Deposits metric is >100,000?  

I can change the filter to be my "CERT" table, but then it would exclude Certificates that are less than $100,000.  

Ahh!! It's so close!  BTW, thank you for your continued help on this!! You get litle more than "good karma" so thank you!

[Ashish_Mathur]

You are welcome.  To assist you, i will need your data.  Show me your data and your expected result.

[kaniggit]

Will this let you download my sample data file?

https://drive.google.com/open?id=1ZqHtJbQtcIR-tCy9N4OSm_mWn64wAn9J

I added a visual (table) and a text box with some additional explanation.  A picture is worth a thousand words!

[Ashish_Mathur · Accepted solution]

Hi,

Try this measure

=LargeDepositors = CALCULATE([TotalDeposits],FILTER(VALUES(ACCT[ACCT]),[TotalDeposits]>100000))

Hope this helps.

[kaniggit]

Thank you thank you!!  Of course filtering the PARENT table was the way to go, and using that values function is genius. 

[Ashish_Mathur]

You are welcome.