Forum Discussion

francino's avatar
francino
Frequent Visitor
5 years ago
Solved

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. 

 

Thank you in advance!

 

Need Help
  • amitchandak's avatar
    amitchandak
    5 years ago

    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

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    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()))

    • francino's avatar
      francino
      Frequent Visitor
      5 years ago

      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.