CODEWITHSUNDEEP
excelpowerpivotdax
Shawn
Shawn

Reputation: 1891

PowerPivot - How to Filter Dimension to Get Value

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

FactTable:

DimSponsor:

DimOffice:

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?

Upvotes: 2

Views: 3621

Answers (1)

Shawn
Shawn

Reputation: 1891

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.

Upvotes: 1

Related Questions