Forum Discussion

Bluespark's avatar
Bluespark
New Member
8 years ago

total with allselected

Hi,

 

After fiddling around for a while, without getting the desired result. I would really appreciate feedback from a DAX expert on the best approach:

 

We have a fact table with amounts for consulting rates and consulting per customer, and # of customers:

this is linked to a dimension table measures:

 

What we need is a calculation that calculates the consulting revenue which equals: days per customer * rate * customer. Theoretically not rocket science: using just that calculation works fine on base level but total on aggregations e.g. total months is wrong (or not what we are intending i.e. the total of the monthly results): 

Consulting Revenue:=sumx('CUBE',
[# Average Consulting Days Per Customer]*CALCULATE([# Customers],ALLSELECTED())*CALCULATE([Average Consultant Rate],ALLSELECTED()))

 

Would love to hear about your ideas how to address. one option is to separate the tables but there should be another way?

 

Thank you!