Help with Power Pivot DAX Measures

Question

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 HSP_ACCOUNT Count=

var current_row_Hosp_ID = min(
var current_row_Prim_Payor = min(
var current_row_FY=min(

RETURN

COUNTROWS(
	FILTER(
		ALL(
			'Status Change'[HSP_ACCOUNT_ID]
			&& 
			&& 
		)
	)

This measure comes up with the correct answers 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 feel like I am missing a very basic step, so any assistance would be appreciated.

Answer 1

Hi,

I suggest you post a new thread in: https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/bd-p/DAXCommands

The reason why we recommend posting appropriately is you will get the most qualified pool of respondents, and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us. Thank you for your understanding.

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment". Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.