Measure to calculate average by a column from a different table

[puneetn]

I’m new to Power BI and blocked by a seemingly simple problem. I’m creating a Power BI report that uses two tables: “fact_claims transactions” for claims transactions, and “dim_procedure” for procedure dimension.

“fact_claims_transaction” table contains following columns:

• Claim Number
• Claim Line Number
• ProcedureFK
• Billed Amount

“dim_procedure” table contains following columns:

• ProcedureSK
• Procedure Code
• Procedure Description

One-to-many relationship exists from “dim_procedure” table (column ProcedureSK) to “fact_claims_transactions” table (column ProcedureFK).

The Power BI report being created requires a “Table” visualization consisting of the following columns:

1. Procedure Code
2. Procedure Description
3. Claim Number
4. Claim Line Number
5. Billed Amount
6. Average Billed Amount by Procedure Code.

First five columns are being captured directly from the two tables. And for the sixth column, I've defined a Measure on “fact_claims_transactions” table, but it doesn’t seem to work.

The measure I’ve defined is:

Average Billed Amount by Procedure = CALCULATE(AVERAGE(fact_claim_transactions[Billed Amount]), ALLEXCEPT(dim_procedure, dim_procedure[Procedure Code]))

Instead of giving Average Billed Amount value by Procedure Code, it is giving the Billed Amount value for the line, as shown below:

Could someone help me understand what's the issue here, and how to fix it?

Thanks for your help!

[v-piga-msft · Accepted solution]

Hi @puneetn,

Try this formula below which works for me. Please refer:

Measure =
CALCULATE (
    AVERAGE ( 'fact_claims_transaction'[Billed Amount] ),
    FILTER (
        ALLSELECTED ( dim_procedureProcedureSK ),
        'dim_procedureProcedureSK'[Procedure Code]
            = MAX ( 'dim_procedureProcedureSK'[Procedure Code] )
    )
)

Here is the output.

If you still need help, please share your data sample and your expected output.

Best Regards,

Cherry