powerbi - How to calculate average of `whole number` data type column by Category

Question

I am currently facing an issue where I need to calculate Average of no. of approvers for each contract type. But the issue over here is that the Approver data i.e. (contract_approval_order_1) field is nothing but USER IDs ( whole number data type). Thus Power BI is not calculating the averages properly as shown in the image below.

I tried the following formula in the column:

Average Approver Order 1 Assigned =
DIVIDE (
    COUNT ( view_contracts[contract_approval_order_1] ),
    DISTINCTCOUNT ( view_contracts[contract_id] ),
    0
)

I am expecting 0.5 as the average of the sample data given below.

question from:https://stackoverflow.com/questions/65893506/how-to-calculate-average-of-whole-number-data-type-column-by-category

Answer 1

Use the following dax formula to create a measure:

Average Approver Order 1 Assigned = 
VAR __numerator = 
    CALCULATE( 
        COUNT ( view_contracts[contract_approval_order_1] ), 
        ALLEXCEPT( view_contracts, view_contracts[contract_approval_order_1] ) 
    )
VAR __denominator = 
    CALCULATE( 
        DISTINCTCOUNT ( view_contracts[contract_id] ), 
        ALLEXCEPT( view_contracts, view_contracts[contract_type] ) 
    )

Return 
    DIVIDE( __numerator, __denominator, 0 )

The ALLEXCEPT statement in the denominator variable may be slightly different if there are other filters ar slicers affecting your table.

This is the result