I am working on creating financial statements in PBI. I am running into an issue where I have a calculated measure that is driving the matrix below. The measure is a massive swith function based off our income statement structure.
Financial Value =
IF(
[Net Revenue]<>0,
IF(
ISINSCOPE('Income Statement Structure'[Level 2 Item]),
SWITCH(
SELECTEDVALUE('Income Statement Structure'[Line Item ID]),
"Physician Salaries and Wages",[1. Physician Salaries and Wages],
"Physician Bonus", [2. Physician Bonus],
"Physician Payroll Taxes",'2. Human Capital'[3. Physician Payroll Taxes],
"Physician Benefits", [4. Physician Benefits],
"Physician Prof Dev", '2. Human Capital'[5. Physician Prof Dev],
"Clinical Salaries and Wages",[1. Clinical Salaries and Wages],
"Clinical Bonus", [2. Clinical Bonus],
"Clinical Payroll Taxes",'2. Human Capital'[3. Clinical Payroll Taxes],
"Clinical Benefits", [4. Clinical Benefits],
"Clinical Prof Dev", '2. Human Capital'[5. Clinical Prof Dev],
"Non-Clinical Salaries and Wages",[1. Non-Clinical Salaries and Wages],
"Non-Clinical Bonus", [2. Non-Clinical Bonus],
"Non-Clinical Payroll Taxes",'2. Human Capital'[3. Non-Clinical Payroll Taxes],
"Non-Clinical Benefits", [4. Non-Clinical Benefits],
"Non-Clinical Prof Dev", '2. Human Capital'[5. Non-Clinical Prof Dev],
"Non Physician Comp and Benefits", '2. Human Capital'[Non-Physician Comp & Benefits],
"Dental Supplies",'3. Clinical Supplies and Labs'[1. Dental Supplies],
"Implants & Grafting", '3. Clinical Supplies and Labs'[2. Implants & Grafting],
"Labs", '3. Clinical Supplies and Labs'[3. Labs],
"Medical Equipment", '6. Other General & Administrative'[1. Medical Equipment],
"Office Equipment", '6. Other General & Administrative'[2. Office Equipment],
"Supplies", '6. Other General & Administrative'[3. Supplies],
"Merchant Services", '6. Other General & Administrative'[4. Merchant Services],
"IT", '6. Other General & Administrative'[5. IT],
"Utilities", [6. Utilities],
"Travel", '6. Other General & Administrative'[7. Travel],
"Meals", '6. Other General & Administrative'[8. Meals],
"Malpractice", '6. Other General & Administrative'[9. Malpractice],
"Staff Development", '6. Other General & Administrative'[Staff Development],
"Uniforms", [Uniforms],
"Dues and Subscriptions", '6. Other General & Administrative'[Dues and Subscription],
"Consulting and Professional Services",[Consulting and Professional Services],
"Legal", '6. Other General & Administrative'[Legal],
"Taxes and Insurance",'6. Other General & Administrative'[Taxes and Insurance],
"Start-Up Addbacks", '6. Other General & Administrative'[Start-Up Addbacks]
),
SWITCH(
SELECTEDVALUE('Income Statement Structure'[Level 1 Item]),
"Net Revenue",[Net Revenue],
"Physician Comp and Benefits", '2. Human Capital'[Physician Comp. & Benefits],
"Clinical Comp and Benefits", '2. Human Capital'[Clinical Comp. & Benefits],
"Non-Clinical Comp and Benefits", [Non-Clinical Comp. & Benefits],
"Non Physician Comp and Benefits",'2. Human Capital'[Non-Physician Comp & Benefits],
"Total Human Capital", '2. Human Capital'[Total Human Capital],
"Clinical Supplies and Labs",'3. Clinical Supplies and Labs'[Clinical Supplies and Labs],
"Marketing Expense", '4. Marketing Expense'[Marketing Expense],
"Rent", '5. Rent'[Rent],
"Other General and Administrative", [zOther General & Administrative],
"Total Operating Expense", [Total Operating Expense],
"PRACTICE EBITDA", [PRACTICE EBITDA]
)
))
Our accounting folks would like to be able to drill down the "Act $" column to be able to see the financial row detail. I am struggling to tie the Financials (detail) table to a drill through, because the Financials (detail) table does not have the actual measure to tie it to. The "financial value" measure is being calculated off of a value column.
I would like it to drill through to the table below, where the value column would show Financial Value. Any ideas?
