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 count of only 5 and not (5+5). How to do this in power bi? Thanks.

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])

     

8 Replies

  • ppgandhi11's avatar
    ppgandhi11
    Helper V
    8 years ago

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

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

         

  • Vvelarde's avatar
    Vvelarde
    Community Champion
    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
      Helper V
      8 years ago

      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!