Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Maverick92
Frequent Visitor

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

Hello, 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 ID`s ( whole number data type). Thus power bi is not calculating the averages properly as shown in the image below.

 

I did tried following formula in the column:

 

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

 

sample data.PNG

Any help would really appreciated! I am open to try any different formula to achieve desire result. Also I would like to try it in DAX instead of query editor. Will look forward to any reponse.

 

1 ACCEPTED SOLUTION

Hi @Maverick92 ,

 

I modified the measure. Because there are multiple contract_types in your sample data, so I used the ALLEXCEPT function.

Average Approver Order 1 Assigned (sample data set) = 
DIVIDE (
    CALCULATE (
        COUNT ( 'sample data set'[contract_approval_order_1] ),
        ALLEXCEPT ( 'sample data set', 'sample data set'[contract_approval_order_1],'sample data set'[contract_type])
    ),
    CALCULATE (
        DISTINCTCOUNT ( 'sample data set'[contract_id] ),
        ALLEXCEPT(  'sample data set','sample data set'[contract_type] )
    )
)

10.png

 

You can check more details from here.

 

 

Best Regards,

Stephen Tao

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

10 REPLIES 10
Maverick92
Frequent Visitor

Hello,

I am currently struggling to create % of Total Sessions based on following formula=

 

Total No. of Session ID`s of each feature / Total No. of Session ID`s by User Types

 

Main hindrance I am currently facing is to calculate `Total Session ID`s by User Type` which need to constant. I tried following formula where I am using AllEXCEPT function to create column .eg:

 

test session count by user types = CALCULATE(DISTINCTCOUNTNOBLANK(user_hits[Session_id_hit_at_date]),ALLEXCEPT(user_hits,user_hits[user_type]))
 
But its not showing valid data because its not matching with what I get by doing normal Dcount on Session id which we could get directly on the field. 
 
 

1.PNG

Here the 'Count of Session_id_hit_at_date` is normal distinct count of Session _id_hit_at_date which we could achieve by right clicking in the field section.

2.PNG

 

 

While `Test Session Count by User Types` is a column calculated using formula mentioned above.

Please let me know how can I achieve result which is there in the second column in the screenshot above using DAX.

 

Please note : I need this total value to be constant even if i drill it to another level. I tried this formula before : Distinctcount(user_hits[sessions_id_hit_at_date]) but this total always change if I put additional column in the visualization.

 

Looking forward to any help on this!

 

Regards,

Jayant Mandhare

 

 

 

 

v-stephen-msft
Community Support
Community Support

Hi @Maverick92 ,

 

Please try this measure

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

2.png

 

You can check more details from here.

 

 

Best Regards,

Stephen Tao

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hello @v-stephen-msft ,

 

Thank you for taking time to send your solution but by some reason it didnt work on my database!

Maybe because of limited dataset you are working with. Please have a look into this file where i inserted sample data table. Click here 

 

I did tried your solution in the same pbix file which you sent earlier but its giving me different results.

 

Let me know what I could do differently to attend the results at large data scale.

 

Regards,

Hi @Maverick92 ,


Could you tell me if your problem has been solved?
If it is, kindly Accept it as the solution. More people will benefit from it.
Or you are still confused about it, please provide me with more details about your problem.


Best Regards,
Stephen Tao

Hi @Maverick92 ,

 

I modified the measure. Because there are multiple contract_types in your sample data, so I used the ALLEXCEPT function.

Average Approver Order 1 Assigned (sample data set) = 
DIVIDE (
    CALCULATE (
        COUNT ( 'sample data set'[contract_approval_order_1] ),
        ALLEXCEPT ( 'sample data set', 'sample data set'[contract_approval_order_1],'sample data set'[contract_type])
    ),
    CALCULATE (
        DISTINCTCOUNT ( 'sample data set'[contract_id] ),
        ALLEXCEPT(  'sample data set','sample data set'[contract_type] )
    )
)

10.png

 

You can check more details from here.

 

 

Best Regards,

Stephen Tao

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Anonymous
Not applicable

Hello @Maverick92 
Can you share your expected output?

Hello @Anonymous ,

Thank you for your response!

As you see in this sample data (Contract Approval_order_1) field which is id`s of contract approvers there are 2 unique id`s in it and the contracts they are handling are 4 unique contracts. so the calculation should be 2/4 = 0.5

Thus the expected output is 0.5 for the field `Average Approver Order 1 Assigned Field`.

This needs to be calculated for each contract type.( I have just shown only one contract type in this example for simplicity purpose- there are more than 20 contract types). Looking forward to your reply.

SAMPLE DATA 2.PNG

 

Anonymous
Not applicable

Hello @Maverick92 
You can try a calculated column based on the below calculation.

Average = DIVIDE(DISTINCTCOUNT('Table (4)'[Contract_approveal_order_q]), COUNT('Table (4)'[Contract_id]))
Capture.PNG

Hello @Anonymous ,

 

Thank you for getting back to me!

I tried your solution but it showcase the same result which I had earlier.

Maverick92_0-1611602614059.png

I am getting 0.44. Not sure why is it so. Could you shed more light on how did you got the result. Is there anything which you done differently regarding the data types of contract_id and Contract_approval_order_1 column? In my data both of these columns are whole numbers.

 

Regards

just FYI - I have tried following formula as well where I have used Distinctcountnoblank but still the values are not matching properly -

 

Average Approver Order 1 Assigned = Divide(DISTINCTCOUNTNOBLANK(view_contracts[contract_approval_order_1]),DISTINCTCOUNT(view_contracts[contract_id]),0)
 
Sample data 3.PNG

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.