Search code examples
bar-chartpercentagespotfire

Spotfire : Weighted Percentage Calculation in BarChart

I have this set of data : 

Kind     Weight  
A        3  
A        3
A        3
B        2
B        2
C        1

I would like to create a Spotfire barchart representing the contribution of distinct Kind column values. The expression provided by Spotfire in this case is : 

Count() / THEN [Value] / Sum([Value]) OVER (All([Axis.X]))

But I would like to divide this by the value of the Weight column. As Spotfire asks for an aggregating method I tried this unsuccessfully :

Count() / First([Weight]) THEN [Value] / Sum([Value]) OVER (All([Axis.X]))

For the record, I am not using a calculated value because my purpose is to have a dynamic BarChart.

Thanks in advance.

Solution

  • Based on your desired logic, you can use this on your VALUE AXIS of your Bar Chart:

    UniqueCount([Kind]) / UniqueCount([Kind]) OVER (All([Axis.X]))

    This will work unless the Weight doesn't equal the Count of Kind

    If your Weight could change, for example if Kind A had a weight of 2 instead of 3 but still had 3 rows, you can accomplish your logic by doing this:

    1. Insert a calculated column: Count([Kind]) OVER ([Kind]) / Max([Weight]) OVER ([Kind]). Name this column WeightedWeight
    2. Use this formula on the VALUE AXIS of your bar chart Max([WeightedWeight]) / UniqueCount([Kind]) OVER (All([Axis.X]))