How to get filtered vs. unfiltered values to create a lift/index value

[SLS]

I am trying to create a lift or index value for a filtered value vs. unfiltered.

Example: One has a database of patients and hypertension incidence (all totally made up):

Unfiltered, the db has 10% True and 90% False. But if one filters for age 65+, the incidence becomes 50% True and 50% False. The lift or index value would be 500, or 5.0...however you express it, indicating a 5x effect of age on incidence (in theory). 

So the question is "How does get both the unfiltered and the filtered together, and be able to divide them to come up with the lift?"

This is not very hard to do in Excel, but we want to do it in Power BI, and so far this has proven difficult unless I'm missing something easy. We also need to be able to do this across multiple tables.

I have been trying to use things like ALL or ALLSELECTED, and COUNTAX to mirror the unfiltered value before trying to modify it. But nothing I have tried has really worked. 

I appreciate any help! Thanks.

[Aron_Moore]

How about this:

Lift =

var RegularPerc = CALCULATE(COUNT(Table1[Patient]),Table1[Hyper]="True")/COUNT(Table1[Patient])

var UnfilterPerc =  CALCULATE(COUNT(Table1[Patient]),ALL(Table1),Table1[Hyper]="True")/CALCULATE(COUNT(Table1[Patient]),ALL(Table1))

return

DIVIDE(RegularPerc,UnfilterPerc)

[SLS]

For what I'm actually trying to do, rather than the example, I don't think I can actually specify True or False. The filter could be 1,000 different things or more. 

I tried not using those parts at all figuring the page filter would do the work, and I get an error:

"A function 'CALCULATE' has been used in a True/False expression that is used as a table filter expression. This is not allowed."

If I try to use CALCULATETABLE other things go wrong. It's really quite hard to believe this is difficult considering you can do it in excel very easily.

[Aron_Moore]

What would your excel formula look like?

[SLS]

What do you mean? You'd just have value 1 in one column, value 2 in another column, and divide 1 by 2, or 2 by 1. Say % of US total population by state from 2010 to 2015, or 2009, or whatever. 

[Aron_Moore]

Okay. What do the Excel formula look like for column 1 and column 2?

[SLS]

I don't know that the answer to that is going to be informative, but this might be...

Using a past data model, we had a team put everything into one table in Power BI and then did this

Axis = Name
Value = ([D]/[All])*100
D = DISTINCTCOUNT(Table[id]/ 

CALCULATE(DISTINCTCOUNT(Table[id]),ALLEXCEPT(Table,Table[Field1],Table[Field2]...))

All = CALCULATE(DISTINCTCOUNT(Table[id], ALLEXCEPT(Table,Table[Name]/ CALCULATE(DISTINCTCOUNT(Table[id],ALL(Table))

Using DISTINCT vs. not is a matter of what we're interested in, and I don't think changes the general idea of how to do it using multiple tables.

Hope this helps.

[SLS]

I guess it didn't.

[SLS]

Bump.