Static Measure Changing when a slicer is filtered

[dbillups]

Hello,

A bit of an odd question I'm hoping to get help with, please.

I created a measure to calculate the total time my team has available per week to work on requests

Weekly Actual Capacity =

 CALCULATE(

    SUM('Static Capacity'[Weekly Capacity])

)

The sum is taken from a table with all employees and their weekly capacity

This line remains static as expected when slicing through my data except for one slicer that filters by species. For some reason this line that should for all intents and purposes remain static, moves. 

The relationship I have is based on Business Request Type-and therefore should not be affected by anything other than the slicer that filters the business request type

For some Species, the line isn't affected, so I'm trying to understand why it is for some but not others

Thank you kindly in advance for your time and consideration!

[HotChilli · Accepted solution]

This requires more information before it can be solved. (what fields are in the visual, what tables they come from?).

--

From a visual inspection, there may be a problem with the filtering since there is a many-to-many relationship there (which we don't like). 

Also, when measures are written with a CALCULATE, it's going to affect context and I would ask "why is it there?".  Sometimes people have a gut instinct to add CALCULATE to measures.  It may be valid, it may not but it may be part of the problem here.

--

In summary, remove the CALCULATE unless you need it, re-examine the model and think about how you want filtering to work here.

[dbillups]

The many-to-many relationship was 100% the issue!! I created a new table with the business request type and related it to the default_1 and Static Capacity tables. 

Genious! Thank you so, so much! Really appreciate you taking a look!

[dbillups]

Thank you kindly!

I added the Calculate because I didn't know any better to be super honest! This is my first PowerBi project, so I'm learning everything as I go; Mostly from this forum and some from YouTube.

I took out the calculate as you suggested and it didn't fix the issue unfortunately

The fields in this visual are volume which takes the request type and assigns a value to it so we can determine the weekly volume of work. There is one other field, Pre-Crud-which does the exact same thing (same formula just different number assigned).

They all come from one table-the "default_1"
Thinking about the many to many relationship as you said though-I'm not sure how I could build that differently or relate it back to the default-1 table. Maybe that's something I need to figure out.

I guess what I really don't understand is why it's only affected by the one slicer and not any other slicer. And even within that one slicer, some of the species work while a handful of others create that dip in the line.

This is my entire data model. I know it's not right (I'm supposed to have Fact and Dim tables)-but the things I'm reporting on have no relation to one another (It's 27 different sites end users put requests into). One big very wide table was my only viable solution I could come up with build the model while I continued to learn.