Showing posts with label Hierarchy. Show all posts
Showing posts with label Hierarchy. Show all posts

Tuesday, November 29, 2011

AVERAGEX with SUM vs. SUMX with CALCULATE(AVERAGE)

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.

Friday, October 21, 2011

Implementing Parent / Child Hierarchy in PowerPivot

We are currently implementing a Management Information Dashboard with Critical Success Factors and I ran into the problem of parent / child relationships in PowerPivot.
Since we are not working with Denali with the customer, I had to make a workaround to import the organisation structure into PowerPivot.

Special thanks to Alberto Ferrari and Marco Russo who made this possible on SQL Blog.com!


Of course not every project is the same, so I ran into a few issues while walking through the steps of Ferrari & Russo.
The first issue I had was that my ParentID and NodeID were both a VarChar (don't ask me, I don't know why..), so I had to convert / cast them as an INT.
Second, this organisation structure has the first two layers which are always the same and have no other nodes on the same level. So my structure actually starts at level 3. I edited the last select statement to leave out the first NodeID's and also changed the ComputeParentNodeIdAtLevels to not select Level1 and Level2. This worked for me.

Several measures with parent / child relations

Featured Post

Power Apps or Translytical Task Flows?

I think I have gotten this question at least five or six times in the last few months, and with Translytical Task Flows reaching GA in the M...