Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
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...
Solved! Go to Solution.
Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.
https://dropmefiles.com/iup4v
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.
be careful, see i created sum ( '# SALES FACT' [SALES Qty] as a separate measure
Makes no difference - same 80 million result. .:(
Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.
https://dropmefiles.com/iup4v
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..
@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]))
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... 😞
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
94 | |
74 | |
71 | |
64 |
User | Count |
---|---|
143 | |
109 | |
103 | |
82 | |
74 |