Forum Discussion

yaman123's avatar
yaman123
Post Partisan
5 years ago
Solved

Incorrect Total in table

Hi,    I have the measure to count how many members have that ppl category.  No of Members = (IF([Total PPL] = BLANK(),BLANK(),CALCULATE(DISTINCTCOUNT('Payment by Member Query'[MEMBER_CODE])))) ...
Need Help
Show and Tell
Tips and Tricks
  • Jihwan_Kim's avatar
    Jihwan_Kim
    5 years ago

    Hi, yaman123 

    Please try the below measure.

     

    No of Members =
    COUNTROWS (
    FILTER (
    VALUES ( 'Payment by Member Query'[MEMBER_CODE] ),
    NOT ISBLANK ( [Total PPL] )
    )
    )

     

    Hi, My name is Jihwan Kim.

    If this post helps, then please consider accept it as the solution to help other members find it faster.

yaman123's avatar
yaman123
Post Partisan
5 years ago

Great thank you! 

 

Also i would like to calculate the % e.g No of Members / Total. How can this be written. I have a measure and it isnt taking into account the correct figures.

 

% PPL = [No of Members]/CALCULATE(DISTINCTCOUNT('Payment by Member Query'[MEMBER_CODE]),ALLSELECTED())
 
It should give a total of 100% but showing as 97.05%
 
Jihwan_Kim's avatar
Jihwan_Kim
Super User
5 years ago

Hi, yaman123 

Thank you for your feedback.

I think the distinctcount/allselect in  %PPL measure = counting all members including zero sales.

I think this percentage is also useful, but if you don't need this, then you can distinct count all members who has non-zero-sales. Something like below...

 

calculate (

COUNTROWS (
FILTER (
VALUES ( 'Payment by Member Query'[MEMBER_CODE] ),
NOT ISBLANK ( [Total PPL] )
), allselected(PPLBUCKET)
)

 

Thank you.

  • yaman123's avatar
    yaman123
    Post Partisan
    5 years ago

    Hi Jihwan_Kim 

     

    I am getting the results as 1 instead of the percentage 

     

    Sorry if i am not writing the dax query correctly, i have the below query:

     

    Measure = [No of Members]/CALCULATE(COUNTROWS(FILTER(VALUES('Payment by Member Query'[MEMBER_CODE]),NOT ISBLANK([Total Milk PPL]))),ALLSELECTED('Payment by Member Query'[MEMBER_CODE]))
     
     
    • Jihwan_Kim's avatar
      Jihwan_Kim
      Super User
      5 years ago

      Hi,

      if it is OK with you, please share the sample pbix file, then I can try to come up with a more accurate mesure.

       

      thanks.

       

      Hi, My name is Jihwan Kim.

      If this post helps, then please consider accept it as the solution to help other members find it faster.

      • yaman123's avatar
        yaman123
        Post Partisan
        5 years ago

        hI Jihwan_Kim 

         

        Theres a lot of private information in the file. I can share the table and measures.

         

        Measure = [No of Members]/CALCULATE(COUNTROWS(FILTER(VALUES('Payment by Member Query'[MEMBER_CODE]), NOT ISBLANK([Total Milk PPL]))),ALLSELECTED())
         
        % PPL = [No of Members]/CALCULATE(DISTINCTCOUNT('Payment by Member Query'[MEMBER_CODE]),ALLSELECTED())
         
        No of Members =
        COUNTROWS (
        FILTER (
        VALUES ( 'Payment by Member Query'[MEMBER_CODE] ),
        NOT ISBLANK ( [Total Milk PPL] )
        )
        )