Incorrect Total in table

[yaman123]

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

[Jihwan_Kim · Accepted solution]

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]

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]

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]

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]

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]

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

)

)

[Jihwan_Kim]

Hi, terribly sorry to say that please check your last measure.

Is it giving the result = 1 ??

When I tested it, it gave me an error.

[yaman123]

Hi @Jihwan_Kim 

I think i have solved this. I have created a new measure 

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

then i create another measure to divide by no of members. 

Measure = [No of Members]/[Total No of Members]

Is it probably a long winded way of doing it but it gives me the correct figures in the table. 

Thanks for your help, much appreciated!