Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Alice_BI
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]))
 
Alice_BI_0-1677103949672.png

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... 

1 ACCEPTED SOLUTION
Ahmedx
Super User
Super User

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

Screen Capture #352.pngScreen Capture #353.png

View solution in original post

10 REPLIES 10
Ahmedx
Super User
Super User

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

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
Super User
Super User

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

Screen Capture #353.png

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

Ahmedx
Super User
Super User

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

Screen Capture #352.pngScreen Capture #353.png

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!

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

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

 

 



Please mark my post as the solution if this answers your question!
Appreciate your Kudos !!

that didn't work either 😞

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... 😞

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.