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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

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
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.