Good morning.
I am in the process of converting a straight up Excel workbook that I inherited to a Data Model within Excel (our client does not use PowerBI, unfortunately). There are a couple of formulas that essentially evaluate the data and calculate an expected reimbursement value that a) evaluates for duplicate/multiple entries and essentially calculates a single expected reimbursement (e.g. an account with 4 entries sums all the hard-coded expected reimbursements and divides that sum by the number of duplicates and produces a "new" expected reimbursement). I can absolutely add those columns in the Power Query from the raw data (adding the per-row reduced output so that it totals up correctly), but my understanding is that it "makes more sense" to leverage DAX in Power Pivot to get to those results.
I wrote a measure to do this and everything checks out when I put the data output into a Pivot Table with the Account Number as the row. But, the amounts do not total up at the bottom of the table.
Here are the Excel Formulas that I want to make DAX Measures to use in my data model.
Formula to count instances of the account number in the data (checks for the instances of the HSP_ACCOUNT_ID where the PRIM_PAYOR and FY all match):
Excel Column/Table Column: [Duplicates]
=COUNTIFS([HSP_ACCOUNT_ID],[@[HSP_ACCOUNT_ID]],[PRIM_PAYOR],[@[PRIM_PAYOR]],[FY],[@FY])
I was able to create a DAX Measure that does this (modified it from one I found on the web, actually):
Status Change Duplicate HAR Count =
var current_row_Hosp_ID = min('Status Change'[HSP_ACCOUNT_ID])
var current_row_Prim_Payor = min('Status Change'[PRIM_PAYOR])
var current_row_FY=min('Status Change'[BI_ADD_FY])
RETURN
COUNTROWS(
FILTER(
ALL('Status Change'),
'Status Change'[HSP_ACCOUNT_ID] = current_row_Hosp_ID
&& 'Status Change'[PRIM_PAYOR] = current_row_Prim_Payor
&& 'Status Change'[BI_ADD_FY] = current_row_FY
)
)
This measure comes up with the correct results and matches the [Duplicates] table column, but it does not "total" at the end when I put into the Data Model Pivot Table.
The two additional Excel formulas that I want to convert to DAX are as follows (I plan to change the names to something easier to use):
[Current Expected Reimbursement - Duplicates]
=IF([@Duplicates]>1,[@[CURR_EXP_REIM]]/[@Duplicates],[@[CURR_EXP_REIM]])
[Original Expected Reimbursement - Duplicates]
=IF([@[ORIG_EXP_REIM]]="NULL",0,IF([@Duplicates]>1,[@[ORIG_EXP_REIM]]/[@Duplicates],[@[ORIG_EXP_REIM]]))
[Lost Revenue]
=IF([@[ORIG_EXP_REIM]] ="NULL", 0,IFERROR([@[Current expected Reimbursement - duplicates]]-[@[Original expected reimbursement - duplicates]],0))
I took a shot at converting the [Current Expected Reimbursement - Duplicates] conversion to DAX and keep running into calculation errors. My attempt that keeps erroring:
Current Expected Reimbursement - Duplicates =
CALCULATE(
DIVIDE(
'Status Change'[CURR_EXP_REIM],
[Status Change Duplicate HSP_ACCOUNT_ID Count],
0
)
)
Error Message: Calculation error in measure 'Measures Table'[Current Expected Reimbursement - Duplicates]: A single value for column 'CURR_EXP_REIM' in table 'Status Change' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.
I was able to get the Current Expected Reimbursement - Duplicates measure to total by row, by wrapping the numerator in a SUM () statement, but it is still not giving a grand total, nor is able to be sliced by market, date, etc. (which is the ultimate purpose of this exercise).
Current Expected Reimbursement - Duplicates=
CALCULATE(
DIVIDE(
SUM(
'Status Change'[CURR_EXP_REIM]
),
[Status Change Duplicate HAR Count],
0
)
)
I feel like I am missing something very basic, so any assistance would be appreciated.
Darren
