Question Re: Using context with calculated variables in Technology Q&A

Using context with calculated variables

former_member808993 — Thu, 16 Jun 2022 18:13:08 GMT

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 !

Re: Using context with calculated variables

ayman_salem — Thu, 16 Jun 2022 20:32:07 GMT

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

Re: Using context with calculated variables

former_member808993 — Fri, 17 Jun 2022 00:09:57 GMT

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!

Re: Using context with calculated variables

ayman_salem — Fri, 17 Jun 2022 03:33:37 GMT

For your second problem, do the following,

Define the following variable:

v_vProfit: =[vProfit] (type Dimension)

v_vProfit_Count: =Count([Profit Center]) ForEach([Employee Key]; [v_vProfit])

then add [v_vProfit] and [v_vProfit_Count] to your table, and then hide the column [v_vProfit]

....

hope this is what you are looking for

Re: Using context with calculated variables

former_member808993 — Mon, 20 Jun 2022 19:08:24 GMT

Thank you, was able to resolve my issue. I made the BEx query key figure into a dimension in webi and was able to use context around it.

Thanks for your idea! Wouldn't have thought of making the Amount key figure a dimension.