cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Surafel
Helper I
Helper I

Total value is going less when filter is not applied

I am calculating total NCA PPM by using total received from NCA table and the Quantity received from Difot table. The total NCA PPM is correct or more when it is filtered by month, but less when it is not. As it is shown below in the image, total NCA PPM is more when it is filtered by April month, but less when the filter is not applied. 

 

 

Screen Shot 2021-05-13 at 7.34.43 PM.png

Screen Shot 2021-05-13 at 7.35.03 PM.png

 

Here is the formula I am using to calculate total NCA PPM : 

 

 

DIVIDE(SUMX(NCA,NCA[QTY Rejected]),SUMX(RELATEDTABLE(Difot),Difot[Quantity Recieved]))*1000000

 

 

 As you can see, NCA PPM is less when it is not filtered by month (1376), but only 981 when it is not. I suppose the total NCA PPM should have been more when the month filter is not applied. This is confusing and I am not sure what the problem is here. 

1 ACCEPTED SOLUTION
v-kkf-msft
Community Support
Community Support

Hi @Surafel ,

 

In the Total part, the formula calculates (the total value of NCA[QTY Rejected]) / (the total value of Difot[Quantity Recieved]) in the selected range, so there may be some months's value greater than the value when it is not filtered.

 

The following is my sample data, and my measure is:

NCA PPM = 
DIVIDE(
    SUMX(NCA, NCA[QTY Rejected]),
    SUMX(RELATEDTABLE(Difot), Difot[Quantity Recieved])
)

image.pngimage.png

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

2 REPLIES 2
v-kkf-msft
Community Support
Community Support

Hi @Surafel ,

 

In the Total part, the formula calculates (the total value of NCA[QTY Rejected]) / (the total value of Difot[Quantity Recieved]) in the selected range, so there may be some months's value greater than the value when it is not filtered.

 

The following is my sample data, and my measure is:

NCA PPM = 
DIVIDE(
    SUMX(NCA, NCA[QTY Rejected]),
    SUMX(RELATEDTABLE(Difot), Difot[Quantity Recieved])
)

image.pngimage.png

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

amitchandak
Super User IV
Super User IV

@Surafel ,  This calculation seems fine other than one misplaced paranathesis

 

DIVIDE(SUM(NCA[QTY Rejected]),SUMX(RELATEDTABLE(Difot),Difot[Quantity Recieved]))*1000000

 

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

Top Solution Authors
Top Kudoed Authors