How to get a smaller subset?

[ppgandhi11]

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.

[Vvelarde · Accepted solution]

@ppgandhi11

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

[ppgandhi11 · Accepted solution]

@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.

[Vvelarde · Accepted solution]

@ppgandhi11

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

[ppgandhi11]

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!

[ppgandhi11]

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.

[Anonymous]

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

[ppgandhi11]

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.  

[Anonymous]

---

@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

[ppgandhi11 · Accepted solution]

@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.

[Anonymous · Accepted solution]

Measure = SUMX(SUMMARIZE(Duplicate_Removal,Duplicate_Removal[NPI],"UniqNPI",AVERAGE(Duplicate_Removal[#ofService])),[UniqNPI])