Forum Discussion
Helper IHow 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 using a measure. The problem is that when I slice an area or areas it takes that proportionally spread value or values away from those areas. See screenshots below.
I want to maintain that proportional amount shown in Measure 1 (circled in red) and add it to the OverheadAmount (circled in green). For example, if I was to slice East and North out, I would want the $1150.60 and $1131.30 to remain showing while also showing a sum total of $2281.90.
I am assuming I may need to use the ALL function?
Here’s the measure for Measure 1 if information purposes:
Measure 1 =
VAR _unallocated_ = CALCULATE(SUM(OverheadTable[OverheadAmount]),FILTER(ALLSELECTED(OfficeTable), 'OfficeTable'[Office]=BLANK()))
VAR _officename_ = SELECTEDVALUE(OfficeTable[Office])
VAR _prop_ = [%BilledAmounttoOffice]
RETURN IF(_officename_=BLANK(),_unallocated_,_unallocated_*_prop_)
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_)
3 Replies
- Greg_Deckler
Community Champion
efstel Yes, the ALL function sounds like the way to go. Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882
Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2. 
efstelThanks 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
101 North 102 East 103 South 104 West 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 - efstel
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_)
