cancel
Showing results for
Did you mean:
Frequent Visitor

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

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.

1 ACCEPTED SOLUTION
Solution Sage

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

10 REPLIES 10
Solution Sage

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

Frequent Visitor

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.

Solution Sage

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

Frequent Visitor

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

Solution Sage

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

Frequent Visitor

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!

Frequent Visitor

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]))
Responsive Resident

@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]))``````

Frequent Visitor

that didn't work either 😞

Frequent Visitor
`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... 😞