wrmcmahan12
New Member
- Joined
- May 13, 2015
- Messages
- 12
Basically I have been trying to get the calculate/filter function to work for me and in most cases it does, but one measure is giving me issues. The calculation I am trying to do is =IFERROR([OVC QTY]/[OVC Plan],0). The results of this function gives me what I need, however the results are specific to the sub group item (Picture 1). I want the result of the group to be the same for each sub group item so I try to use this formula: =CALCULATE(IFERROR([OVC QTY]/[OVC Plan],0),FILTER(ALL(Query_OR_LC),COUNTROWS(FILTER(Query_OR_LC,EARLIER(Query_OR_LC[Product])=Query_OR_LC[Product]))),FILTER(ALL(Query_OR_LC),COUNTROWS(FILTER(Query_OR_LC,EARLIER(Query_OR_LC[Month])=Query_OR_LC[Month])))). When I use this in the measure, the results are the same for each line item however the results are wrong (E.g. for group beachmold I want each line to show -11.1%, but the results are showing -100.3% I have attached pictures of what the regular output should be and the results that I am getting. I use this same measure filter for other expressions and it works fine so I am at a loss. The only thing I can think of is that in this case, the measure is using a column in a different table. The table are linked and have similar data except one table is for sales, the other is for costs. Some of the subgroups are not in the sales table, but since I am trying to show the same result for each this should not matter correct? Does anyone have any suggestions on what I am doing wrong here? If I need to provide any additional info to clarify what I am doing please let me know!
Table without using the calculate measure [OVC Qty %] = [OVC QTY]/[OVC Plan]). Results
Results when using calculate function. OVC Qty % is the same at each line but the result is wrong
Table without using the calculate measure [OVC Qty %] = [OVC QTY]/[OVC Plan]). Results
Results when using calculate function. OVC Qty % is the same at each line but the result is wrong