Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I am sure this is easily done in power bi, but I need help in this. I want the NPI counted only once. So, A should have count of only 9 and not (9+9+9+9). similarly, B should have count of only 5 and not (5+5). How to do this in power bi? Thanks.
Solved! Go to Solution.
Hi, try with this measure
Measure = IF ( HASONEVALUE ( 'NPI Table'[NPI] ), AVERAGE ( 'NPI Table'[Service] ), SUMX ( SUMMARIZE ( 'NPI Table', 'NPI Table'[NPI], "AVG", AVERAGE ( 'NPI Table'[Service] ) ), [AVG] ) )
Regards
Victor
@Anonymous
Hi Raj,
As I explained in the post, for the given dataset, the result of total should be: 9+5+3 = 17 (A should be counted once with value 9, B should be counted once with a value 5 and C should be counted once with a value 3) . The entire record set I gave was for a specific field: Service.
Please let me know if attached clarifies the doubt. Thanks.
Measure = SUMX(SUMMARIZE(Duplicate_Removal,Duplicate_Removal[NPI],"UniqNPI",AVERAGE(Duplicate_Removal[#ofService])),[UniqNPI])
Hi, try with this measure
Measure = IF ( HASONEVALUE ( 'NPI Table'[NPI] ), AVERAGE ( 'NPI Table'[Service] ), SUMX ( SUMMARIZE ( 'NPI Table', 'NPI Table'[NPI], "AVG", AVERAGE ( 'NPI Table'[Service] ) ), [AVG] ) )
Regards
Victor
Hi @Vvelarde
Your solution works! I just needed a minor tweak. I had a drill through from earlier page so had to adjust it to consider all NPIs using calculate funtion and all(prov). Thanks a lot!
Please note that there are other columns too in the dataset which are used for other purpose so "remove duplicates" will not work in advanced editor. Thanks.
If you just want to display NPI and Service then you can have both columns in the table visial and select 'don't summarize' ' for 'Service' column in the values section as shown below.
Is this what you are looking for?
Thanks
Raj
No. That is not what I am looking for. This is how my data looks like after few manipulations and in one of the visuals. because the systemkey is present, the rows are repeated. I cannot get rid of systemkey. I want to count each NPI only once. So for NPI A, instead of counting 4 times 9, I want to count it only 1 time. As a result in below, my total # of service should be: 9+5+3 = 17. How to get that? I think this should be doable using DAX formulas. Thanks. One thing is guarenteed in the data. If the the combination is A-9, it is always going to be A-9.
@ppgandhi11 wrote:No. That is not what I am looking for. This is how my data looks like after few manipulations and in one of the visuals. because the systemkey is present, the rows are repeated. I cannot get rid of systemkey. I want to count each NPI only once. So for NPI A, instead of counting 4 times 9, I want to count it only 1 time. As a result in below, my total # of service should be: 9+5+3 = 17. How to get that? I think this should be doable using DAX formulas. Thanks. One thing is guarenteed in the data. If the the combination is A-9, it is always going to be A-9.
For this dataset, how do you want to display the output? Could you pls post that as well.
Thanks
Raj
@Anonymous
Hi Raj,
As I explained in the post, for the given dataset, the result of total should be: 9+5+3 = 17 (A should be counted once with value 9, B should be counted once with a value 5 and C should be counted once with a value 3) . The entire record set I gave was for a specific field: Service.
Please let me know if attached clarifies the doubt. Thanks.
Measure = SUMX(SUMMARIZE(Duplicate_Removal,Duplicate_Removal[NPI],"UniqNPI",AVERAGE(Duplicate_Removal[#ofService])),[UniqNPI])
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |