If a calculated row value is blank how to replace it with a Sum?

[Alice_BI]

hi, i've been racking my brain over a seemingly simple problem. I'm working with 2 tables. 1 Merchandise dimension and 1 Sales fact.  1 to many relationship. When the sum of  volume sold (from the fact table) for Wool aparel (from the dimension) is blank for a given year, I need to replace it with the sum of the 2 products above. So instead of "Y" there should be 2,070,173.00 there

if(isblank(sum('#SALES FACT'[Volume Sold])),"Y",sum('# SALES FACT'[Volume Sold]))

It should be SUMX, i need the sum of all rows in that table. I tried 

 if(isblank(sum('#SALES FACT'[Volume Sold])),sumx('#SALES FACT',[Volume Sold]),sum('# SALES FACT'[Sales Volume]))

There is also a year filter on it.

SUMX just returns the sum for all years for Wool apparel only. I dont need wool apparel, I want to replace that calculated value, on that Wool apparel line - only when it's blank - with the sum of all values/non blank rows in that table. In this case there is only 2. 

 i've spent 3 days on this now :'(  please please help... 

[Ahmedx]

 can you show the relationship between the year table and the sales table

[Alice_BI]

hi Ahmed,  i'm just using 2 tables. Sales Fact and merchandise dimension which also has the  Sales Year. Regular Many to one relationship between the two. 

[Ahmedx]

be careful, see i created sum ( '# SALES FACT' [SALES Qty] as a separate measure

[Alice_BI]

Makes no difference - same 80 million result. .:(

[Ahmedx · Accepted solution]

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.
https://dropmefiles.com/iup4v

[Alice_BI]

it didnt' work initially beceasue there was anoter external filter on the merchadise code but once i included that in VAlues to restore the external filter context it worked! Thank you!

[Alice_BI]

Thank you so much for traying to help. I replicated the formula and getting completely wrong numbers  unfortunately. 88 million instead of 2 million. 😞 not sure what i'm doing wrong. Year slicer is on,  but it's not getting the sum of those 2 lines no matter what year i pick..

Blank Sales QTY =

Var _isoBlank = isblank(sum('# SALES FACT'[Sales Qty]))

Var _tbl = CALCULATETABLE('# SALES FACT',REMOVEFILTERS(),values('Merchandise Dim'[Sales Year]))

var _SumForBlank = sumx(_tbl, '# SALES FACT'[Sales Qty])

return if(_isoBlank,_SumForBlank,sum('# SALES FACT'[SALES Qty]))

[johnyip]

@Alice_BI , I

Please try this to see if it works. I haven't test that; this is an quick answer comes up in my mind and I don't have the sample data from you though.

Blank Sales QTY =

Var SelecedYear = ALLSELECTED('Merchandise Dim'[Sales Year])
Var _isoBlank = isblank(sum('# SALES FACT'[Sales Qty]))
Var _tbl = CALCULATETABLE('# SALES FACT',REMOVEFILTERS(),values('Merchandise Dim'[Sales Year]))
var _SumForBlank = sumx(FILTER(_tbl,[Sales Year]=SelecedYear), '# SALES FACT'[Sales Qty])

return if(_isoBlank,_SumForBlank,sum('# SALES FACT'[SALES Qty]))

[Alice_BI]

that didn't work either 😞

[Alice_BI]

sumx(FILTER(_tbl,[Sales Year]=SelecedYear)

There is no such measure as Sales year - it's just a field in the dim table.. It expects a measure here... wouldnnt let me select a single field... 😞