Forum Discussion
If Measure Repeating All Possible Row Combinations
I have an IF measure, made up of two other measaures. They are:
EstimatedMargin = [SUMTotalAmt]-[EstimatedTotalCost]
EstimatedTotalCost = SUM(SalesHistory[Estimated LaborCost])+SUM(SalesHistory[Estimated MaterialCost])+SUM(SalesHistory[Estimated OtherCost])+SUM(SalesHistory[Estimated SubcontractorCost])
EstimatedMarginIF = IF([EstimatedTotalCost]=BLANK(),"No Cost Bid",[EstimatedMargin])
EstimatedMargin and EstimatedTotalCost each work fine on their own.
EstiatedMarginIF is trying to say that if Total Cost is blank, meaning that none of the cost-fields were given values, then return "No Cost Bid", otherwise, return EstimatedMargin.
I'm including a screenshot to try and explain what I'm seeing. Basically, the measure returns a correct value for the first line, but then duplicates itself as "No Cost Bid", for all possible combinations of each column that are in the matrix.
In the following example, I've filtered to a single SO number (7176537), a single branch (BVLS Sun City), and a single Market (Florida West). I did not filter on region. I expect the top line to return as it did. However, the following two lines are, are what the measure is fabricating. These are not accurate Region -> Market -> Branch intersections, the market and branch belong to Florida North, not Florida South, or Southeast. Further, if I were to unfilter on Market, it would return every market, for each region. Further further, if I were to unfilter branch, I'd get every branch, across every market, across every region. As you can imagine, this errors out the program and I can't use it.
I've tried to filter across all measures to mitigate this problem, but nothing seems to work.
Any ideas?
3 Replies
Anonymous
The formula seems right. I need to check with some data.
Can you share sample data and sample output.
