CODEWITHSUNDEEP
exceldaxpowerpivot
Carl-Philip
Carl-Philip

Reputation: 3

How to determine the order in summarize and calculate values in power pivot

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.

Upvotes: 0

Views: 168

Answers (1)

Brett
Brett

Reputation: 931

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           |

Upvotes: 0

Related Questions