How to get calculate function to display group result on each sub group level Power Query

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
Before_zpse9mr479z.png



Results when using calculate function. OVC Qty % is the same at each line but the result is wrong
After_zpsrfytszjs.png
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

Forum statistics

Threads
1,223,893
Messages
6,175,240
Members
452,621
Latest member
Laura_PinksBTHFT

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top