I want to create a calculated field where I need sum of num_of_pos column for all unique hrg_req_id and want to show this number as a Number Card/KPI Card . Please tell me the DAX query/Formula

Screenshot (26)

@sourceease - Thank you for posting your query! Is your KPI visual filtered for a particular hrg_req_id? In what context you want to show the value? Ideally a simple sum(num_of_pos) is what you should be using and that value will change based on whatever data your filters are limiting. Let me know if this help.

if I do simple sum of num_of_pos then it will return sum all rows like in given screenshot it will return 15 but I want total sum should be equals to 6 (2+1+1+1+1) which is calculated once corresponding to distinct hrg_req_id .
I hope now you understood my problem ?

@sourceease - Thanks for the details. Please try to create a calculated field with the following expression:

sum(avg({num_of_pos}, [{hrq_req_id}]))

I have tried to replicate the solution at my end with an example dataset and it is working. Sharing the snapshot as well for reference. Let me know if this helps!

Did my suggestion help you in resolving your query? If yes, would request you to mark the post as “Solution”. This will help the community to find guidance and answers to similar question. Thank you!


Thanks sugmukhe for resolving the issue!

@sourceease - Glad that it helped! May I request you please mark the relevant post as “Solution”. That would help the community to find solution to similar problems!