12 Replies Latest reply on Dec 13, 2016 9:34 AM by Walt Reed

# Count based on calculations?

Hi,

I am trying to count the following category types:

I have a formula that will highlight the number for each product category RED if it exceeds a certain threshold.

Name of formula: Category Calc

IIF(Sum([Number of Records])>=AVG([Global Thresholds]),'True',IIF(SUM([Number of Records])<AVG([Global Thresholds])AND SUM([Number of Records])>= 0.8*(AVG([Global Thresholds])),'Near','False'))

Each of the categories above are "True", so therefore they are highlighted red.

I can't seem to find a way to count the number of rows that have met the threshold of "True".

I tried...

If [Category Calc]="True" THEN COUNTD(Platform) END

But it doesn't return the number that I want, which is "4".

Could someone provide some insight?

Thanks!

Sam

• ###### 1. Re: Count based on calculations?

Hey Sam,

For the calculation you described, what number does it return?

• ###### 2. Re: Count based on calculations?

Hi Walt,

So there are a total of 100 Product Categories Total. Of these, based on the "Category Calc" feature, there are a total of 16 categories that are "True" as in the number of occurrences has exceeded the thresholds.  However, the number I get back is 48.

The screen shot I provided was just 4 out of the 16, but follows the same principle.

Sam

• ###### 3. Re: Count based on calculations?

Thanks Sam. So from what I gather, the "Category Calc" is added as a filter, and only "True" values are kept? If so, try adding the filter to the context and see if that does it. If it doesn't, are you able to attach a packaged workbook that we could examine?

Walt

• ###### 4. Re: Count based on calculations?

Try the calc below then sum those values?

IF [Category Calc] = "True"

THEN 1

ELSE 0

END

• ###### 5. Re: Count based on calculations?

Hi Walt,

Sam

• ###### 6. Re: Count based on calculations?

Hey Sam,

Thanks for attaching the workbook. I had some success with: WINDOW_COUNT(COUNT([Product Family])), then compute using: Table (down).

Walt

• ###### 7. Re: Count based on calculations?

Hi Walt!

Thanks for taking a crack at it, but where exactly would I fit this formula?

I was trying to create a text based scoreboard where it would be the below image except to be able to count as "3" actionable events since only 3/8 of the Product Families reached it. Could you walk me through how you would incorporate your formula into this concept? Thanks!!

• ###### 8. Re: Count based on calculations?

Yeah, so it depends on where you want the value to show. If you wanted to put it in the Label, just create the calculated field and add to label, then customize the label from there:

Walt

• ###### 9. Re: Count based on calculations?

Hi Walt,

Very close...I would ideally like just one single box that says "3" Platforms rather than have it split up by the categories and just repeating.

Sam

• ###### 10. Re: Count based on calculations?

Okay, I see what you're saying. For this part, I had to re-create the Global Calc formula using LOD calculations (it's a long formula):

Then I create a logical field to filter only when the Global Calc = True:

Add this field to the filter, then add the new Global Calc to Text, change to COUNT([Global Calc (copy)]), modify the Text, and you've got the desired result:

Walt

• ###### 11. Re: Count based on calculations?

Did you get a "Can not mix aggregate and non-aggregate arguments with this function error?

• ###### 12. Re: Count based on calculations?

I'm not at my computer at the moment but I believe I had to convert either

Incidents or Global Threshold from dimension to measure, and that should

fix it.

Walt