Power BI summarized or grouped? How to work out ratio

Question

I have spent hours trying to solve and I dont' even know what to search an answer for:

This is my base data table in Power BI

PA	PA FTE	PERSON	PERSON FTE
Anne	1.0	Peter	1.0
Anne	1.0	Simon	1.0
Anne	1.0	James	0.5
Sue	1.0	Andrew	1.0

To help myself, I have created a grouped table, grouped by PA & PA FTE

PA	PA FTE	SUM of PERSON FTE
Anne	1.0	2.5
Sue	1.0	1.0

I want to display on a card the result of TOTAL SUM OF PERSON FTE / TOTAL SUM OF PT FTE

Or 1.75 (which is 3.5/2.0)

If I try and create a measure on my grouped table like this:

MEASURE = SUM([PERSON FTE])/SUM([PA FTE])

I DO NOT GET 1.75

Answer 1

It's better to separate out the 2 tables. When I did, Power BI automatically created a relationship between the 2 tables, which is what was throwing my total off and maybe yours too. Deleting the relationship fixed it.

My result:

My model: Be sure there is no relationship between the tables.

My DAX:

Total Person FTE = SUM(Person[PERSON FTE])
Total PA FTE = SUM(PA[PA FTE])
Ratio = DIVIDE( [Total Person FTE], [Total PA FTE] )

My Power Query:

// Data
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcszLS1XSUTLUMwCSAaklqUVQXqwOmmRwZm5+Hi5Jr8Tc1GIgbaBnCpYMLkXIOealFKWWw3TGAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [PA = _t, #"PA FTE" = _t, PERSON = _t, #"PERSON FTE" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"PA", type text}, {"PA FTE", Int64.Type}, {"PERSON", type text}, {"PERSON FTE", type number}})
in
    #"Changed Type"

// PA
let
    Source = Data,
    #"Removed Other Columns" = Table.SelectColumns(Source,{"PA", "PA FTE"}),
    #"Removed Duplicates" = Table.Distinct(#"Removed Other Columns"),
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Duplicates",{{"PA FTE", type number}})
in
    #"Changed Type"

// Person
let
    Source = Data,
    #"Removed Other Columns" = Table.SelectColumns(Source,{"PA", "PERSON", "PERSON FTE"})
in
    #"Removed Other Columns"