7 Replies Latest reply on May 9, 2018 5:04 AM by Michael Hesser

# Find the count of marks

Hello Team,

1. I have created Table calculation to find the Percentage of difference on top of the sales

2. Also, created a KPI field to indicate GOOD or BAD based on the Percentage of difference

3. I want to find out the Count of Marks (in this case Months) for each KPI type

My Expectation (In this example):

GOOD - 4

• ###### 1. Re: Find the count of marks

Where you want to display the count??

• ###### 2. Re: Find the count of marks

Hi Chandu;

You may be able to do this with a couple of WINDOW_COUNT expressions

Count GOOD

window_count(sum(iif([KPI]="GOOD",1,null)))

See if that gives you the results you're after! Good Luck--Michael

Note: As muhammad muhib indicates, where you want this detail to go may affect your calculation. A window_count won't be effective if you want a simple screen listing total counts; you'll likely need to use a LOD for that.

• ###### 3. Re: Find the count of marks

Thanks for looking into this. I want to use them in new sheet. or like a summary card.

• ###### 4. Re: Find the count of marks

Hi Michael,

Thanks for input, I will try this.

• ###### 5. Re: Find the count of marks

For a summary card, you'll need a LOD expression since your counts won't technically be in the "window."

Something like this may work, based on your layout:

Count KPIs

{FIXED [your datepart variable],[Segment], [KPI]: Countd([KPI])}

Place KPI dimension on ROWS and use [Count KPIs] as your Text.

• ###### 6. Re: Find the count of marks

Hi Michael,

I tried the LOD approach. But getting the error with KPI table calculation as below

• ###### 7. Re: Find the count of marks

Chandu-- can you include a TWBX?

This formula seemed to work for me using superstore:

KPI

{FIXED DATEPART('year', [Order Date]),DATEPART('month', [Order Date]):(iif(SUM([Sales])>45000,"Good","Bad"))}

//Filters come first, followed by your KPI formula which I made up in this case

I dropped KPI (the dimension) on Rows, and CNT(KPI) in Text.

Please let me know if this works for you. I feel like there's a simpler LOD expression that will work so perhaps someone else will buzz in!