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]))))
 
But this is not returning the correct total at the bottom of the table. The total i am getting is 711 but it should be 690. 
 
I would like the measure to only calculate is the value is not blank in the table.
 
 
TIA 
 
Yasir
 
 
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.

8 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    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.