Forum Discussion
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
1 Reply
- lbendlin
Super User
Please provide sample data that covers your issue or question completely.
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
Please show the expected outcome based on the sample data you provided.
https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523