Spotfire : Weighted Percentage Calculation in BarChart

1
votes

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.

1
bar-chartpercentagespotfire
what would your expected results look like? Can you attach the bar chart showing what it is rendering compared to what your desired output is? – scsimon
I would like to have a bar chart where in my exemple A, B and C have the same value which is 33%. The 3 occurences of A are divided by their weight, also for B and C. Currently, A is at 50%, B at 33% and C at 17% – saad0n87
Well C is 1 so 1/1 is 100%... how would that be 33%? A should be 33 and B should be 50... how do you get 33 for all of them? – scsimon
Let me detail my computation : C is 1/1, B is 2/2, A is 3/3 so each one is at 33% – saad0n87

1 Answers

1
votes

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]))