cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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.

1 ACCEPTED SOLUTION
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.

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.

5 REPLIES 5
Community Support

Hi @FRG

You may try to update your measure as below.

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

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.

Resolver I

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

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.

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.

Resolver I

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

Super User

Announcements

#### Fabric certifications survey

Certification feedback opportunity for the community.

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors