Forum Discussion
Formula Help
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
4 Replies
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/n4TYhF2ARe8Hello Greg_Deckler,
None of these worked. I am open to new solutions. I keep getting circular dependency errors.
Thank you,
Fools_Gold
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.
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
