Forum Discussion

efstel's avatar
efstel
Icon for Helper I rankHelper I
2 years ago
Solved

How do I Slice and avoid filtering a measured value?

I have a spread of amounts that are allocated over four areas plus an amount that is not allocated to an area. I have taken that unallocated amount and spread it proportionally over the other areas u...
  • efstel's avatar
    efstel
    2 years ago

    I figured this out... Rather simple fix. Instead of FILTER(ALLSELECTED(OfficeTable) it needed to be FILTER(ALL(OfficeTable).

    Measure 1ALL =
    VAR _unallocated_ = CALCULATE(SUM(OverheadTable[OverheadAmount]), FILTER(ALL(OfficeTable), 'OfficeTable'[Office] = BLANK()))
    VAR _officename_ = SELECTEDVALUE(OfficeTable[Office])
    VAR _prop_ = [%BilledAmounttoOffice]
    RETURN IF(_officename_ = BLANK(), _unallocated_, _unallocated_ * _prop_)

     

efstel's avatar
efstel
Icon for Helper I rankHelper I
2 years ago

Thanks Greg_Deckler 

I have three tables. See below. 

Expected output: When sliced just for "East" for example, I want to maintain that proportional amount shown in Measure 1, $202.60 and add it to the OverheadAmount, $948 for a sum total of $1150.60. If I slice just East, the $202.60 drops from the formula.

Explanation: Billings for each area divided by Total billings of all areas determine percentage proportion for each area (%BilledAmounttoOffice). Take the unallocated amount of overhead UnallocatedOverhead multiplied by %BilledAmounttoOffice to give me Measure 1. Add Measure 1 to OverheadAmount to give me Total Office Overhead (Measure 1 plus OverheadAmount) for each Office. Then I want to be able to slice it and maintain the  Measure 1 plus OverheadAmount without it filtering the Measure 1.  

Hope this clarifies it a bit better. 

 

 

idOffice

101North
102East
103South
104West

 

idBilledAmount
101$123
102$165
103$207
104$249
101$291
102$333
103$375
104$417
101$459
102$501
103$543
104$585
101$627
102$669
103$711
104$753
101$795
102$837
103$879
104$921
101$963
102$1,005
103$1,047
104$1,089
101$1,131
102$1,173
103$1,215
104$1,257
101$1,299
102$1,341
103$1,383
104$1,425

 

idOverheadAmountUnallocatedOverhead
101$100 
102$101 
103$102 
104$103 
 $104$104
101$105 
102$106 
103$107 
104$108 
 $109$109
101$110 
102$111 
103$112 
104$113 
 $114$114
101$115 
102$116 
103$117 
104$118 
 $119$119
101$120 
102$121 
103$122 
104$123 
 $124$124
101$125 
102$126 
103$127 
104$128 
 $129$129
101$130 
102$131 
103$132 
104$133 
 $134$134
101$135 
102$136 
103$137 
104$138