I'm still working on the Management Information Dashboard I previously talked about when I was Implementing Parent / Child Hierarchy. (Note: There are some Dutch words and expressions in the examples)
Now I ran into this issue when trying to calculate the number of clients of a particular type of cost center (Activiteitencentrum, Ambulant, etc.).
My implemented hierarchy is in the Organisatie table, I'm interested in the organization type Woonlocatie and I want to calculate the number of clients per Region / Cost center. I have the Region and Type (both of table Organisatie) in the row label, where Year is in my column label.
I first tried this with Measure1:
AVERAGEX(FILTER(Organisatie, Organisatie[Type] = "Woonlocatie"), SUM(Productie_OrganisatieGroei[AantalClienten]))
But Measure1 does not calculate the totals per region correctly, it takes all the clients into account, not only where type="Woonlocatie". Then I tried Measure2:
SUMX(Productie_OrganisatieGroei, CALCULATE(AVERAGE(Productie_OrganisatieGroei[AantalClienten]), Organisatie[Type] = "Woonlocatie"))
which does take into account the organization type in the hierarchy. I'm not all clear about why this is working correctly with Measure2 and not with 1. If anyone does know why or has something to share about this, I'd like to know.
Measure1: AVERAGEX(FILTER(Organisatie, Organisatie[Type] = "Woonlocatie"), SUM(Productie_OrganisatieGroei[AantalClienten]))
Measure2: SUMX(Productie_OrganisatieGroei, CALCULATE(AVERAGE(Productie_OrganisatieGroei[AantalClienten]), Organisatie[Type] = "Woonlocatie"))
[Edit]
A few months later I participated in the 2-day SSAS Tabular workshop from Marco Russo and Alberto Ferrari. After the workshop I had a better understanding why measure 2 is working correctly, please see my follow-up post.
