How display on chart calculated columns

23 Views

I have a table with:

Date, ID, [Counter]

I queried ID and [counter] filtering Date between Date1 and Date2 that results in:

ID, Sum([Counter])

Depending on the number of days of available data I defined a Threshold ( [numberOfDays]*0.9*86400) and a Root that defined the ID as LOW or HIGH or Data N/A (in case of null values).

The final result is something like this:

ID
Sum([Counter]) numberOfDays
Threshold

Root

2-T004069_2-T004070 86392 5 86300 HIGH 4-T002255_4-T002256 86400 6 86402 LOW 4-T003189_4-T003190 86399 6 86402 LOW 2-T006307_2-T006308 86400 6 86402 LOW 5-T009977_5-T009978 86399 5 86300 HIGH 5-T010825_5-T010826 (null) 5 86300 Data N/A

I need to create a pie-chart with the percentage of ID HIGH, ID LOW and ID without data.

I've created a Dimension variable with the 3 Root value ( If(IsNull(Sum([Counter]))Then "Data N/A" ElseIf(Sum([Counter]>=Threshold) Then "HIGH" Else "LOW" ).

I've created a Measure Counter ( Count(ID) )

The expected result is a table:

Root Count

Data N/A 1

HIGH 4

LOW 1

I'm receiving the following:

Root Count

Data N/A 1

HIGH 0

LOW 5

It seems that It recalculates on the fly the Threshold aggregating all days queried instead for each ID. In the example the Threshold on the fly is 86402.

Where is the fault? Can you show me step by step the procedure? I wonder why It's recalculating the threshold corrupting the final Count getting from query not from report result.

Thank you for your help,

GreenRobot