Replace negative and blank with zero and don't show months without forecast hrs

[itsme]

I have a few things going on here and I'm hoping someone can help me. The image below shows Net Forecast Hours (NFH).

The yellow highlights show cells that should be zero and the red lines indicate that August and September should not be showing in the table viz because there are not any Total Forecast Hrs in those months. If there are no Total Forecast Hrs in a given month, then I don't want Net Forecast Hrs to show, even if there are Holiday Hrs or PTO Hrs for those months. Also, when Total Forecast Hrs are present but the but Net Forecast Hrs are negative, I want it to show 0.

Below is a snapshot of the data model and measures I am using and all the information you may find useful.

Net Forecast Hrs = [Total Forecast Hrs] - [Holiday Hrs SUMX] - [PTO Hrs]

Total Forecast Hrs = SUM ( 'Project Forecast'[Hours] )

PTO Hrs = CALCULATE ( SUM ( PTO[Hours] ), 'Project Assignment' )

Holiday Hrs SUMX = SUMX ( VALUES ( 'Calendar'[Date] ), CALCULATE ( SUMX ( VALUES ( 'Project Forecast'[Consultant] ), [Holiday Hrs] ) ) )

Holiday Hrs = SUM ( Holiday[Hours] )

I also want PTO Hrs to show zero on these blanks, but when I add + 0 to the formula the other matrix viz with NFH populates with all the months in the calendar even if there are no forecast hrs for the months. I'm not sure how to adjust the measure for this filter context. Also notice the NFH column where I highlighted the values that should show 0 instead of negative numbers.

Thank you!

[Anonymous]

Hi   @itsme ,

You can try the method in the following links to convert the blank value to 0:

1. POWER BI PRO TIP: SHOW 0 INSTEAD OF (BLANK)  

TotalSales = COALESCE(SUM('Table'[Sales]),0)

2. show items with no data as 0 instead of blanks

TotalSales= IF ( ISBLANK ( SUM ( 'Table'[Sales] ) ), 0, SUM ( 'Table'[Sales] ) )

TotalSales= SUM ( 'Table'[Sales] ) + 0

Showing an alternate text when no data available in a Power BI chart visuals

3. Replace NULL with 0 in matrix

 

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards

[Smalfly]

Hi @itsme ,

you could try to calculate the max of 0 and your measure. That should turn the negative values into zeroes.

Once you've done that, you can use a (hidden) filter on NFH > 0.

[Anonymous]

If you want to show zeros instead blank you can use COALESCE function.

COALESCE(Measure)

[itsme]

@Anonymous that does not work. COALESCE requires more than one argument. If I use COALESCE( Measure, 0 ) for PTO Hrs, I run into the same issue mentioned earlier where NFH starts populating in additional matrix columns or other category values where Total Forecast Hours is zero...basically unnecessary rows and column values equal to 0. Same thing if I use COALESCE on NFH.