topic Re: Aggregate grouping rollup sum in Power Query

Aggregate grouping rollup sum

rtech2022 — Wed, 12 Jan 2022 08:42:39 GMT

Hi there, looking for suggestions to get a aggregated sum based on a defined grouping. In this example, i would like to see the grouping by ProdDate + Shift

I tried using the summarize function.

The desired output would look like this ..

Table relationship

i can provide the sample .pbix if that easier to provide feedback.

Re: Aggregate grouping rollup sum

HotChilli — Wed, 12 Jan 2022 13:06:25 GMT

Yes please, link the pbix and i'll have a look

Re: Aggregate grouping rollup sum

rtech2022 — Wed, 12 Jan 2022 13:19:58 GMT

Thank you, pbix file attached.

sample_prod.pbix

Re: Aggregate grouping rollup sum

rtech2022 — Wed, 12 Jan 2022 13:22:19 GMT

File attached - thank you,

Re: Aggregate grouping rollup sum

Anonymous — Tue, 18 Jan 2022 08:34:43 GMT

Hi rtech2022 ,

If you want to get the sum of [prod_target] for each shift, your final output should be

for D——4365.16+4583.42=8948.59 ， for N ——4583.42. Actually I'm confused why N is 9166.84.

My method:

Measure = var _t= SUMMARIZE(FILTER(ALLSELECTED('tbl_Mining'),[Shift]=MAX('tbl_Mining'[Shift])),"sum of each shift",SUM('tbl_Target'[Production Target])) return SUMX(_t,[sum of each shift])

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Aggregate grouping rollup sum

rtech2022 — Tue, 18 Jan 2022 10:00:01 GMT

Thank you Evelyn, that has resolved my issue in calculating the shift target. You were correct the value for N should have been 4583.42.

Now that we have the shift target which is the measure we calculated. I can calculate the Split Bench Ratio using :

Split Bench Ratio % =
DIVIDE([Prod Target], [Shift_Target])

FYI: Prod Target =
VAR __BASELINE_VALUE = SUM('Target'[Production Target])
RETURN
IF(
HASONEVALUE(Mining[From Location]),
__BASELINE_VALUE, SUMX(VALUES(Mining[ProdDate]), __BASELINE_VALUE))

Weighted Shift Target =
[Prod Target] * Mining[Split Bench Ratio %]

The next challenge i have is calculating the BCM Var Weighted ..

The logic should be simple as (Mining[BCM] -Weighted Shift Target )/Weighted Shift Target

This is what i am getting -

This is the output i am working towards .. and stuck on the calculations for ..

Weighted BCM Var%

AVG Weighted BCM Var%

Daily Average

Any assistance would be greatly appreciated

Re: Aggregate grouping rollup sum

Anonymous — Wed, 19 Jan 2022 06:40:10 GMT

Hi rtech2022 ,

Please try:

Weighted BCM Var% = (SUM('tbl_Mining'[BCM])-[Weighted Shift Target] ) /[Weighted Shift Target]

AVG Weighted BCM Var% = var _t= SUMMARIZE(ALLSELECTED('tbl_Mining'),[Shift],[FromLocation],"%",[Weighted BCM Var%]) return AVERAGEX(FILTER(_t,[Shift]=MAX('tbl_Mining'[Shift])),[%])

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Aggregate grouping rollup sum

rtech2022 — Thu, 20 Jan 2022 04:08:22 GMT

Hi,

It's very close - any suggestions on how to get the totals to sum correctly.

I tried something like this but it did not work.

Weighted Shift Target1 =
--[Prod Target] * [Split Bench Ratio %]
VAR T1=[Weighted Shift Target]
VAR T5 =SUMMARIZE (
FILTER(ALLSELECTED('tbl_Mining'),[Shift]=MAX('tbl_Mining'[Shift]) && [ProdDate]=MAX('tbl_Mining'[ProdDate]))
,'tbl_Mining'[ProdDate]
,'tbl_Mining'[Shift]
-- ,'tbl_Mining'[FromLocation]
, "SPLIT1", [Weighted Shift Target]) --Shift Target

return
IF (HASONEVALUE('tbl_Mining'[ProdDate]),
SUMX(T5, [SPLIT1] ),
SUMX(T5, [SPLIT1] ))

Re: Aggregate grouping rollup sum

Anonymous — Mon, 24 Jan 2022 09:32:53 GMT

Hi rtech2022 ,

Could you please kindly Accept my helpful reply as the solution to make the thread. More people will benefit from it.

And actually I'd suggest you create a new thread for your second issue to get further help.

Best Regards,
Eyelyn Qin