Formula Help

[Fools_Gold]

Hello Everyone,

I am trying to write a formula where it adds only the negative in QTY Gap (and replaces the positive with zero) and a formula where it adds only the positive in QTY Gap (replacing the negative with zero) and I cannot seem to figure it out.

I already tried the new visual calculations:

Negative QTY = IF(QTY GAP<0,QTY GAP,0)

Positive QTY = IF(QTY GAP>0,QTY GAP,0)

But the total for the new visual calculations are not summing.

I have also tried column calculations as well.

QTY GAP Formula = ('Custom Report 6'[SalessHistory QTY] + 'Custom Report 6'[Open Order QTY]) - 'Custom Report 6'[Forecast QTY]

Thank you for your help.

Fools_Gold

[Fools_Gold]

Hello @Greg_Deckler,

Thank you for your help.

Currently, I have shipped, open order, total qty (shipped + open order), Fcst QTY, and QTY Gap (Total QTY - Fcst QTY). I am trying to add Negative QTY (which pulls the negative numbers from QTY Gap into a new column (see below)) and Positive QTY (which pulls the positive numbers from QTY Gap into a new column (see below)) and sums each one individually like below. When I do an "IF" statement using the new visual calculation the total does not sum correctly.

Thank you,

Fools_Gold

[Greg_Deckler]

@Fools_Gold First, please vote for this idea: https://ideas.powerbi.com/ideas/idea/?ideaid=082203f1-594f-4ba7-ac87-bb91096c742e

This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

Also: https://youtu.be/uXRriTN0cfY
And: https://youtu.be/n4TYhF2ARe8

[Fools_Gold]

Hello @Greg_Deckler,

None of these worked. I am open to new solutions. I keep getting circular dependency errors.

Thank you,

Fools_Gold

[Greg_Deckler]

@Fools_Gold Can you provide some sample data and information on how to recreate your visual/calculations? 

Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.