Forum Discussion

ppgandhi11's avatar
ppgandhi11
Helper V
8 years ago
Solved

How to get a smaller subset?

      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 coun...
Need Help
  • Vvelarde's avatar
    Vvelarde
    8 years ago

    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's avatar
    ppgandhi11
    8 years ago

    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's avatar
    Anonymous
    8 years ago

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

     

ppgandhi11's avatar
ppgandhi11
Helper V
8 years ago

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's avatar
Anonymous
Not applicable
8 years ago

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