Issue with Power Pivot measures & slicers

[Jaunkalns]

Hello,

I've come across an annoying "feature" while trying to create an Excel based report.
Basically, I've created a data model using Power Pivot and added a couple simple measures - Forecast Accuracy (FA) & BIAS (see screenshot).
After adding them to pivot tables everything seems to be correct and working properly, but after adding some slicers one of the measures (FA) is displaying the results in a weird way.
When I try to filter a specific country (using the country field from the unique list used for creating relationships), the rest do not disappear, but are shown as 100.0% (see screenshot). Choosing to use a filter using the country field from the same data source works fine (but, of course, cannot the be linked with other tables in my report).
The other measure (BIAS) works perfectly fine.

I have two main data sources that originate from the same original data table and are linked/connected using relationships via helper tables.
The reason for this is the fact that the full data is on client level (more detailed) while FA is calculated on product level for each country (it uses absolute value difference between forecasted & actual sales volumes and is not calculated properly otherwise). I could not find a better way to solve this than grouping the client level data using Power Query.
I still need to analyze the client level data, so it is crucial to have it as well.

Not sure where the problem might be, so any ideas on how to fix this or even approach it differently altogether will be greatly appreciated.

 

[Matt Allington]

Which tables are the slicers connected to?

 

[Jaunkalns]

For example, one of the pivots that is not filtering as expected is set up as following:
Year/Month & M_FA (this is a measure) taken from Material level
MaterialId & ProductDescription from Material list

Slicers connected - CountryLevel1, Division, UmbrellaBrand (all from their respectful helper lists in the middle)

So, when selecting country level in the slicer, it does not filter out the MaterialId that are not present in the specific country but just leaves it as 100%

 

[Jaunkalns]

But if I switch the measure to M_BIAS (with everything else remaining the same) the pivot is filtered as expected.

 

[Jaunkalns]

Figured it out - the issue was with the M_FA measure, as the "1-" part of the function applies to the whole dataset and is constant.
So even if the "DIVIDE(SUM([ABS Deliveries]), SUM([Total Deliveries])" part returns nothing, the measure is still calculated and returns 1 (or 100% when converted to percentages).

Adjusted it and now everything works as expected:
=IF(
SUM([ABS Deliveries]) = 0,
BLANK(),
1-DIVIDE(SUM([ABS Deliveries]),SUM([Deliveries])))