Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
kaniggit
Frequent Visitor

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

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.

1 ACCEPTED SOLUTION

Hi,

 

Try this measure

 

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

 

Hope this helps.

 

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

13 REPLIES 13
Ashish_Mathur
Super User
Super User

Hi,

 

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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"

 

 

schemaschema

 

Hi,

 

Try this

 

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

 

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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

Hi,

 

Sorry, try this

 

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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?  

Hi,

 

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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!

 

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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!

Hi,

 

Try this measure

 

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

 

Hope this helps.

 

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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

You are welcome.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.