Filter within calculated measure / custom ratio with filter

Questions & Answers
, ,
1

Hello,

I want to make a calculated measure that is a ratio of number fields while filtering some of the values asymetricly.

I have 2 number fields (Planned and Actual) and a 3rd qualifying field (Condition) on my tickets.

I want to make a ratio of Actual / Planned while filtering some of Planned based on the Condition value.

Tickets Planned Actual Condition
BI-1 10 5 Optional
BI-2 10 10 Mandatory
BI-3 10 10 Mandatory
BI-4 10 10 Mandatory
results → 117%

In excel that would look like =SUM(C2:C5/SUMIF(D2:D5,“Mandatory”,B2:B5))

Any idea how to do so?

I tried Filter() but couldn’t get it to work

Any help appreciated

2

Okay, I think I might have managed to do it with the so called tuples ().

Ratio =
[Measures].[Actual] / ([Measures].[Planned], [Condition].[Mandatory])

I still need to confirm this really works.

1 Like