Calculated Measure Disappears When Filtered

[stevemcgrath]

Hi PowerBI Jedis

Need some help please!

I've used a disconnected measure table to create measures that present as custom totals and ratios to present in line with 'normal' line items (as per below).

To use a specific example, let's take 'Staff Related - Total' above. I've got some columns that allow me to create custom groups, so the specific measure for 'Staff Related - Total' is:

_Measure - Staff Related - Total =

CALCULATE (

[_Measure - Base - Normal Sum],

ALL ( data_LineItems[Line Item Display], 'data_LineItems'[Order],'data_LineItems'[Default Visibility?]),

data_LineItems[Expense Group 3] = "Staff Related - Total")

(Note - Line Item Display is the values in the 'Line' column of the table above, and the Order and Default Visibility are just in there so that it presents correctly)

To get the data into the table, I've got a measure that does something like this (simplified, there are multiple total measures):

_Measure - Base - Sum v Total =
VAR var_Measure =IF(HASONEVALUE(data_LineItems[Line Item Display]),VALUES(data_LineItems[Line Item Display]),BLANK())
RETURN
SWITCH(var_Measure,
"Staff Related - Total",[_Measure - Staff Related - Total],
[_Measure - Base - Normal Sum])

Then I apply time intelligence over the '_Measure - Base - Sum v Total' to get the YTD / LY YTD / Variance columns.

So far so good, everything displaying correctly. Now here's the problem...

If I filter that table just for one or more of these total rows, nothing displays:

If I add one other thing to the filter that is in the 'data_D365Mapped' table, then all of a sudden both display (and the Staff Related - Total calculates correctly, regardless of what I've selected).

The relationship between the 'data_D365Mapped' table and the line item table is that 'Line Item Display' field.

What I'm mainly trying to use this for is to show something like 'Staff Related - Total' in a table with locations as the rows, and pick the line item from a slicer, where I'm getting the same outcome. But the above illustrates the issue (and hopefully the solution) in the same way.

I've got no idea what is going on, what is even more frustrating is that I've copied this from another BI file which is very similar, and I don't seem to get the same problem - totals, ratios etc. present fine when that is all that is filtered.

What's going on, I'm going crazy here!!!

[v-zhangti]

Hi, @stevemcgrath 

You try this formula.

_Measure - Staff Related - Total =
CALCULATE (
    [_Measure - Base - Normal Sum],
    FILTER (
        ALL ( data_LineItems ),
        data_LineItems[Expense Group 3] = "Staff Related - Total"
    )
)

If not, can you provide the PBIX file for testing? Sensitive information can be removed in advance. What kind of expected results do you expect? You can also show it with pictures. I look forward to your response.

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.