Removal of values in KPI/ Line chart/ Pie chart without limiting filter capablity

[Anonymous]

Hi all,

I have a table with a column named "basket" containing many repeated values of 1,2,3,4,5. (Egg baskets with a different number)

Each row represents one egg.

I want to have a dashboard ONLY concerning the dimension which values are 1,2 and 3. (I want to omitted 4 and 5 completely)

For example, i want to count the total number of eggs in baskets 1,2 and 3. I want to present this data as a KPI.

Measure: count({$<basket={'1'}>} basket) + count({$<basket={'2'}>} basket)  + count({$<basket={'3'}>} basket)

Next, i want to create a pie chart to show the proportions of eggs among baskets 1,2 and 3.

Dimension: basket

Measure: count({$<basket={'1'}>} basket) + count({$<basket={'2'}>} basket)  + count({$<basket={'3'}>} basket)

When the user clicks on a pie (say basket 1), i want the KPI created originally to change its value to counting the eggs in basket 1 rather than all three baskets. How do i go about this without loading a new table which omits basket 4 and 5. Am i going about this wrongly.

Regards,

Gaston

[micheledenardi · Accepted solution]

Hi Gaston,

first of all change your pie chart expression from:

count({$<basket={'1'}>} basket) + count({$<basket={'2'}>} basket)  + count({$<basket={'3'}>} basket)

to:

count({$<basket={'1','2','3'}>}basket) //it's more elegant

and then use if() with getfieldselections() formula to achieve your goal, so this is the formula for the kpi:

=if(isnull(GetFieldSelections(basket)),

'# of 1,2,3 basket' & chr(13)& chr(13)&count({<basket={'1','2','3'}>}basket),

'# of '&GetFieldSelections(basket)& 'basket' & chr(13)& chr(13)&count(basket))

[Anonymous]

Hi Michele,

Thank you it worked! May i bother you by asking you the functions of '# and chr(13)

Regards,

Gaston

[micheledenardi]

chr(13) is the ascii code for carriage return.

# is the abbreviation of "number"