Using IF statement to create categories by exclude total

[bo_wang5]

All 

I'm trying to sum "Won Q3" by "Forecast Status" and "Status"  here are my measures and I'm not getting the correct totals.

 

Sum Won Q3 = CALCULATE(sum(Data[ASO: Amount (converted)]),filter(Data,Data[Data Entry or Type]="Won-Q3"))

Sum 6+6 = CALCULATE(sum(Data[ASO: Amount (converted)]),filter(data,Data[Data Entry or Type]="6+6"))

Sum 7+5

Sum 8+4 are written similar to Sum 6+6

Forecast Status = If([Sum 6+6]=0,if([Sum 7+5]=0,if([Sum 8+4]=0,"not in forecast","in forecast"),"in forecast"),"in forecast")

Status = if([Sum Won Q3]<=0,"Not Won","won")

 In forecast and won = if([Forecast Status]="in forecast",if([Status]="won",[Sum Won Q3],0))

I think this is not working because I show a total in my "Status" and "Forecast Status" columns, but I'm not sure how to filter out that total because it is meaningless since it's a category.

[v-easonf-msft · Accepted solution]

Hi, @bo_wang5 

If you want to remove these meaningless totals, you use IF +HASONEVALUE  to replace the total value.

New_Forecast Status = 
IF ( HASONEVALUE ( Data[ASO:Opportunity] ), [Forecast Status], BLANK () )

New Status = 
IF ( HASONEVALUE ( Data[ASO:Opportunity] ), [Status], BLANK () )

New In forecast and won = 
IF (
    HASONEVALUE ( Data[ASO:Opportunity] ),
    [In forecast and won],
    SUMX ( VALUES ( Data[ASO:Opportunity] ), [In forecast and won] )
)

Best Regards,
Community Support Team _ Eason