PowerPivot - How to Filter Dimension to Get Value

2
votes

I am working on a PowerPivot report that has the following tables/relationships:

FactTable:

  • SponsorId
  • OfficeId
  • Type
  • Color
  • Quantity

DimSponsor:

  • SponsorId
  • SponsorName
  • ProjectName

DimOffice:

  • OfficeId
  • Region
  • Country
  • Manager
  • NumberEmployees

Relationships exist FactTable to each of the two dimension tables.

I wish to create a calculated measure that is the SUM(Quantity)/SUM(NumberEmployees) which represents the average number of items processed by number of employees.

I believe I am having the problem listed here at PowerPivotPro FAQ

When I create a pivot table that has an X-Axis of [Region] with [NumberEmployees] as the only value it always displays the sum of ALL [NumberEmployees] regardless of any slicer selection (in this case, I created a horizontal slicer of [SponsorName]).

I am new to DAX and have struggled to find a formula to meet my needs. Is there any solution to this problem?

1
excelpowerpivotdax

1 Answers

1
votes

I found working examples of what I needed to accomplish on Marc Russo's blob. It is a matter of creating a custom measure that filters the Office table by the already filtered rows of FactTable.

As some of the comments on the page mention, it isn't as easy as drag-n-drop but it works.