Forum Discussion
Self-join table dynamically
Hello community,
I'm new to Power BI, and i've been going through the forums here, but i didn't understand how to solve this problem:
I have a table with values for different kpis, like:
Product, KPI, month, value
A, 1, 1, 10
A, 1, 2, 11
A, 1, 3, 12
A, 2, 1, 20
A, 2, 2, 21
A, 2, 3, 22
B, 1, 1, 30
B, 1, 2, 31
.....
In our previous approaches, with other tools, we used to programatically join the table with itself, for each KPI, on (product and month), resulting in something like:
Product, month, value(kpi1), value(kpi2)
A, 1, 10, 20
A, 2, 11, 21
A, 3, 12, 32
B, 1, 30,
B, 2, 31,
I'm still stuck into this approach philosophy, and haven't been able to achieve this in Power BI.
My actual purpose is to compute operations between different KPIs of the same product, during the same month, as for example KPI 1 x KPI 2:
Product, month , value (KPI 1 x KPI 2)
A, 1, 200
A, 2, 231
A, 3, 384
B, 1, 0
B, 2, 0
I've kept trying to add a new column with the corresponding values, both in the data view or in the query step, without luck.
On the other hand, what would the best approach in Power BI be, to do such an append for all KPIs dynamically, without knowing their values?
Sorry if i missed any good help topics, if anyone can at least point me in the right direction?
Thank you for any hints,