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

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