Forum Discussion

iker_b's avatar
iker_b
Frequent Visitor
3 years ago

Calculate dynamic sumproduct based on a column

Hi everyone!

 

I´m trying to plot a sumproduct based on two columns. I have a column "KW" representing the week number, and another two called "Variante" which determines types, and "Est" which also represents types. I also have "TC" to determine how much time does each operation take, and "Rep" to show how many operations of each type ("KW"-"Variante"-"Est") have occurred. As you can imagine, you can select different ranges for KW, so it is dynamic.

 

When I choose one "Variante" and many "Est", I don´t have any problem because I already calculate that value previously, so I can plot it directly. But when I choose one "Est" and many "Variante", I have to take into account the amount the events of that Variante. As you can see in the image below, the point in the upper chart (93) is just the average between the values of the points in the chart below (89 and 97), but I should get the sumproduct of the table on the left (97*190/(190+19)) + (89*19/(190+19)) = 96,27.  

 

How can I create a measure to plot it in the upper chart (or even just by using a card) to create this calculation? As I said, it should depend on the filters I apply, so if I select 5 Variante, it should recalculate them, or if I select more than one KW.

 

BTW, the data contains one line per each combination of KW-Est-Variante.

 

I hope I have managed to explain it clearly, and thanks for the help!

When I choose just a value for KW, a value