Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
Solved! Go to 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"
)
)
Proud to be a Super User!
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"
)
)
Proud to be a Super User!
User | Count |
---|---|
98 | |
89 | |
82 | |
70 | |
67 |
User | Count |
---|---|
114 | |
104 | |
101 | |
72 | |
65 |