How to count rows based on criteria

[Tasos]

Hello,

I have written a code that is incorrect as it always returns 0.

I have the following data, where in the same day I have multiple orders.

 

What I am trying to find is in how many days the total daily quantity exceeds a specific value (Avg+sel. Stdev). In the example below, the answer should be 6.

 

The code I have written is: COUNTROWS(filter(OrderBase,sumx(SUMMARIZE(OrderBase,OrderBase[received], "daily", SUM(OrderBase[Quantity])),[daily]) >[Avg+sel.Stdev]))

"Orderbase" is the table I have the data, "received" is the transactional date and "Quantity" is the number of units.

Can anyone assist to fix it?

[Greg_Deckler]

Perhaps try:

Measure = 
COUNTROWS(
     FILTER(
          SUMMARIZE(
               ALL(OrderBase),
               OrderBase[received], 
               "daily", 
               SUM(OrderBase[Quantity])
          ),
          [daily]) > [Avg+sel.Stdev])
)

Could also be an issue with your [Avg+sel.Stdev], is that a measure and can you post the formula for it?

[Tasos]

Hello Greg,

Thank you for your answer. 

Having used your code, I have an error that "the expression refers to multiple columns. Multiple columns cannot be converted to a scalar value".

The [Avg+sel.Stdev] is a measure, however, it is correct as I have used it in another case. When I replaced the [Avg+sel.Stdev] with 0 in my code, it returned 40K. This is the total number of lines. What I understand is that as I have written the code, it filters each line separately instead of the summarised values I am looking for.

Hope that it is clear?

[Greg_Deckler]

OK, I just wrote this measure using the VanArsdel data:

Measure = 
COUNTROWS(
  FILTER(
    SUMMARIZE(
      ALL(Sales),
      Sales[Date],
      "daily",
      SUM(Sales[Units])
    ),
    [daily]>100
  )
)

I didn't get that error. My concern with your [Avg+sel.Stdev] measure is context. If the context in which it is executing is causing it to return something that you don't expect then it could be messing up your filter.

[Tasos]

You are right, I had used your code incorrectly. In addition, when I used a number instead of the Avg+sel.Stdev, it worked.

The code for Avg+sel.Stdev is  (SUM(OrderBase[Quantity])/DISTINCTCOUNT(OrderBase[received]))+(CALCULATE(STDEVX.s(SUMMARIZE(OrderBase,OrderBase[received],"value",SUM(OrderBase[Quantity])),[value])))*max('Std Deviation'[Z])

What I have tried to do in this measure is to calculate the average plus a specific standard deviation (Z Std Deviations as I called it below)

When I am using this measure in a table, it is working though.

  

I am confused now, any further assistance is highly appreciated.

[Anonymous]

Hi @Tasos,

Measure formula will be filter and summary by current row contents, but if you used on card visual, current row contents will be expand to whole table level.(e.g. function which used to get current row contents will get wrong result, it will try to calculate on whole table level)

For this scenario, you need to add more condition to force calculate on specific records.

Regards,

Xiaoxin Sheng