cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
FRG
Resolver I
Resolver I

Remove negative value of measure from total

Hi all,

I have 3 main tables; one for piece ready to produce (tbl_to_produce), one for the produced piece (tbl_produced) and one for the NO_PROD (det_comm) which is the table who link the 2 other table (related by NO_PROD).
I need to calculate the difference between tbl_to_produce and tbl_produced for each NO_PROD on a selected date. That is working well. I also need to remove the negative value for each NO_PROD in the total. That is what I'm not able to do because I can not just use a filter in the table visual, I want to create a report with a line graph, and it's not allowed with this visual. 
Here is my sample data with the formulas.
Thanks for your time!

https://www.dropbox.com/s/666xean7gz61mu9/Sample.pbix?dl=0 

1 ACCEPTED SOLUTION
RicoZhou
Community Support
Community Support

Hi @FRG 

You can calculate the sum by summarize function and sumx function.

Measure:

Measure = 
SUMX(SUMMARIZE(FILTER(DET_COMM,[Qty_remains_to _produce]>0),DET_COMM[No_Prod],"Remain",[Qty_remains_to _produce]),[Remain])

Result is as below.

1.png

Best Regards,

Rico Zhou

 

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

5 REPLIES 5
RicoZhou
Community Support
Community Support

Hi @FRG 

You may try to update your measure as below.

IF([Qty_remains_to_produce]<0,BLANK(),[Qty_remains_to_produce])

If this reply still couldn't help you solve your problem, please tell me more details about your calculate logic.

I have downloaded your sample and found all result you got in measure Qty_remains_to_produce <0.

Please provide me a screenshot of the result you want to compare with the result shown in your sample.

This may make it easier for me to understand your requirement.

 

Best Regards,

Rico Zhou

 

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

Hi @RicoZhou ,

The result I'm looking for is highlighted in yellow just bellow. To do this I only use a filter in the table visual: Qty_remains_to_produce > 0. But I can't use this filter in a visual where No_Prod does'nt appear. My formula needs to give the good result in a card visual.
I'm closer of the expected result if I use GROUPBY but grand total is not right.

CALCULATE([Qty_to_produce_date] - [Qty_produced_date];GROUPBY(tbl_to_produce;tbl_to_produce[NO_PROD]))

Thank you

FRG_1-1606151922795.png

 

 

RicoZhou
Community Support
Community Support

Hi @FRG 

You can calculate the sum by summarize function and sumx function.

Measure:

Measure = 
SUMX(SUMMARIZE(FILTER(DET_COMM,[Qty_remains_to _produce]>0),DET_COMM[No_Prod],"Remain",[Qty_remains_to _produce]),[Remain])

Result is as below.

1.png

Best Regards,

Rico Zhou

 

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

Hi @RicoZhou ,

Exactly what I was looking for! Thank you for your precious time.

amitchandak
Super User
Super User

@FRG , isinscope and hasonevalue should help you to change the grand total formula

https://www.kasperonbi.com/use-isinscope-to-get-the-right-hierarchy-level-in-dax/

https://powerpivotpro.com/2013/03/hasonevalue-vs-isfiltered-vs-hasonefilter/

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

Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.