KPI: How to divide Grand totals by each other?

Phil Bongiovanni

Good afternoon,

I'm trying to create a KPI for a dashboard called "Win Rate %." I am trying to take the SUM of the first column (CO Line Flag) and divide it by the count of the 2nd column. So, in the below example it would be 9/10 = 90%.

Here is the report expression I'm using the CO Line Flag: IIF([CO Create Date: CO Line Count] > 0,1,0). The second column "CO Line Count" is just the count of CO Lines. [CO Create Date: CO Line Count].

This is the positional calc I used but it's not working the way I want. (IIF([CO Create Date: CO Line Count] > 0,1,0)/[CO Create Date: CO Line Count]{[CO Lines.Total]})

thank you,

Diane McElhiney

The syntax seems to be correct. Below is a test using your construct. The only difference I see is no specified aggregation for CO Line Count….[CO Create Date: Count: CO Line Count]

BQL:

SELECT TOP 100 USING OUTER JOIN IIF([OrderDate: Sum: Quantity]>5000,[OrderDate: Sum: Quantity],0) / [OrderDate: Sum: Quantity]{[Product.Total]} * 100 'test2' , [OrderDate: Sum: Quantity] 'Quantity' , [OrderDate: Sum: Quantity] / [OrderDate: Sum: Quantity]{[Product.Total]} * 100 'test' , [Product.CategoryName] 'CategoryName' FROM [ALL]