cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## Count products below a user defined threshold

I am having trouble with getting a count of products below a user selected threshold.  Here is the scenario.  I have a table with products and their respective sales:

I calculate the average sales using a measure.  Then I factor that average using a what if scenario to arrive at a threshold.

(MEASURE) Ave Sales = CALCULATE(sum('Table1'[Sales])/DISTINCTCOUNT('Table1'[Unit]))

Factor = 40% --> from a user defined What if Scenario

(MEASURE) Threshold = [Ave Sales]*('Factor'[Factor Value])

Ave Sales = 151.25

Factor = 40%

Threshold = 60.5

What I need to know is the number of products below that threshold.  In this example, that number should be 2.

I know that measures cannot be used to determine a subset of data in another measure.  I have tried dividing the Sales into the Threshold and counting the #'s less than 1 and several other approaches.

1 ACCEPTED SOLUTION
Super User

@francino , Try measure

GT Threshold =
var _t = calculate([Threshold], allselected(Table))
return
countx(values(table[product]), if([sales] > _T,[product], blank()))

Below Threshold =
var _t = calculate([Threshold], allselected(Table))
return
countx(values(table[product]), if([sales] <_T,[product], blank()))

2 REPLIES 2
Super User

@francino , Try measure

GT Threshold =
var _t = calculate([Threshold], allselected(Table))
return
countx(values(table[product]), if([sales] > _T,[product], blank()))

Below Threshold =
var _t = calculate([Threshold], allselected(Table))
return
countx(values(table[product]), if([sales] <_T,[product], blank()))

Frequent Visitor

Thank you!  That worked.  I made just one small change to the variable by replacing your code with the Threshold measure and it all came together.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

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

#### Fabric Community Update - August 2024

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

Top Solution Authors
Top Kudoed Authors