i am very new to power pivot and there is this one thing I haven't been able to understand fully. I have this table consisting of Week, value 1 and Value 2.
I want to first summarize all the values for week 1,2,3 and so forth and then divide the sum of value 1 with the sum of value 2. However, when i do a measure power pivot first divide value 1 with value 2 on each row and then summarize them.
This probably is a very basic question but if someone could shed some light on this for me I would be more than happy.
It is not clear what the resulting table you would to see is and this is important to understand in order to determine the correct DAX for a measure.
However given the following input data in table "tablename"
| Week | Value 1 | Value 2 |
| 2018 w1 | 200 | 4 |
| 2018 w2 | 300 | 5 |
| 2018 w3 | 250 | 3 |
| 2018 w4 | 100 | 4 |
The most obvious measure would be
Value1 by Value2 =
divide
( calculate(sum('tablename'[Value 1]))
, calculate(sum('tablename'[Value 2]))
)
This would mean that if you brought this into a table with Week in the context then you would get the following
| Week | Value 1 | Value 2 | Value1 by Value2 |
| 2018 w1 | 200 | 4 | 50 |
| 2018 w2 | 300 | 5 | 60 |
| 2018 w3 | 250 | 3 | 83.33 |
| 2018 w4 | 100 | 4 | 25 |
or if you used this for all weeks your table would be
| Value1 by Value2 |
| 53.125 |