Forum Discussion
Measure behaviour in a table
Hi,
I would like to create a measure that calculates an average Rank. This rank is measured across all my records and repeated against a category. I want to calculate the average of this rank against a second category. So for example:
data looks like this:
cat1 cat2 rank
A one 10
A two 10
B one 13
B three 13
C three 40
I would like this to calculate the average rank of all cat1 by displaying it by cat2 this way:
cat2 avgRank
one avg(10 and 13)
two avg(10)
three avg( 13 and 40)
many thanks!
Assuming your fact table is called 'fTable', try:
Average by Cat 2 = CALCULATE ( AVERAGE ( fTable[Rank] ), ALLEXCEPT ( fTable, fTable[Cat 2] ) )
6 Replies
Assuming your fact table is called 'fTable', try:
Average by Cat 2 = CALCULATE ( AVERAGE ( fTable[Rank] ), ALLEXCEPT ( fTable, fTable[Cat 2] ) )sorry, made a mistake to the table:
cat1 cat2 rank
A one 10
A two 10
B one 12
B three 12
C three 15
so the rank will always be the same across cat1 but it will be different for each cat2. But if you want to display each of the cat2 and their average rank by cat1 so:
one avg(10 and 11)
two avg(10)
three avg(12 and 15)
Why is Three 12 and 15?
Your formula works fine for the first scenario, it's just that it doesn't change and will always be the same in my case.
