Using context with calculated variables

[former_member808993]

Hi,

Hoping to get some answers. I have the 5 green columns as calculated measures.

I want to remove Employee - Key and keep Tenure & Classification dim and summarize it like the 2nd table.

It should summarize as

If (vProfit & VPosition & vPay & vTenure & vClass) = 1, Then Unmodified Amount = 1.0000 Else Unmodified Amount for each Employee - Key. What I have in Orange column is the desired output.

I've tried numerous ways but is it possible to use context in calculated figures? When I remove Employee - Key from the table, all the calculated figures (green column) just sum up and the above if statement breaks because it is no longer looking at by Employee - Key.

Thank you !

[ayman_salem · Accepted solution]

The variable should look like this:

v_Amount: =Sum (If ([vClass] = 1 And [vPay] = 1 And [VPosition] = 1 And [vProfit] = 1 And [vTenure] = 1) Then 1 Else [Unmodified Amount]) ForEach ([Employee Key])

...

hope this is what you are looking for

[former_member808993 · Accepted solution]

I think my issue is the calculated green columns are measures and not dimensions. I made a sample webi report like you did with the values as dimensions and I'm getting the right results like you are.

When I remove Employee - Key and Profit Center and add count of profit center for each Employee - Key, ideally I should get the 2nd table below. But I'm getting the 3rd table.

Making the measure a dimension doesn't work, I played around with making it a text so it doesn't auto sum but that's not working either. How can I get vProfit for each employee - key without it summing it up to 3.

Thank you!