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.