Calculate Subcategory Totals. Then divide all Subcategories by sum otwo specific subcategory totals.

[Anonymous]

I could not find my specific issue in an existing post. Any help will be appreaciated. 

I have a the table below (table1). This table references Jobcodes in (table2) 

Table 1

Jobcode	FTE's
1	6
2	7.06
3	31.04
4	23
5	14

Table2

Jobcode	Subcat	Cat
1	APP	Provider
2	Ops	Support Staff
3	Clinical	Support Staff
4	Clerical	Support Staff
5	Physician	Provider

I am able to to get the following, but need to divide everything by the Provider total of 20.

Provider	20
Physician	14
APP	6
Support Staff	61.1
Ops	7.06
Clinical	31.04
Clerical	23
Total	81.1

This is what I really need.

Provider	1
Physician	0.7
APP	0.3
Support Staff	3.055
Ops	0.353
Clinical	1.552
Clerical	1.15

This is the formula that gets me closest to what I need. 

Provider Ratio = DIVIDE(Table1[FTE's],CALCULATE(SUM(Table1[FTE's]),OR('Table2'[Subcat]="APP",'Table2'[Subcat]="Physician")))

Provider	1
Physician	0.7
APP	0.3
Total	4.055

I do not understand why it is filtering out my other subcategories/cat totals.

[Anonymous]

@danextian 

THANK YOU!!!!! You are appreciated!

The answer seeems so obvious now... 

[danextian · Accepted solution]

Hi @Anonymous 

Your current formula does what Provider Total2 does in the screenshot below

That is because the filter is removed in Subcat but no Cat. Since both APP and Physicial are under Provider, Support Staff will then return blank. Your formula is very similar to this:

Provider Total2 =
CALCULATE (
    SUM ( fte[FTE's] ),
    FILTER (
        ALL ( jobcode[Subcat] ),
        jobcode[Subcat] = "Physician"
            || jobcode[Subcat] = "APP"
    )
)

Change your formula to this:

Provider Total = 
CALCULATE (
    SUM ( fte[FTE's] ),
    FILTER (
        ALL ( jobcode[Cat], jobcode[Subcat] ),
        jobcode[Subcat] = "Physician"
            || jobcode[Subcat] = "APP"
    )
)