Forum Discussion

rtech2022's avatar
rtech2022
Regular Visitor
4 years ago
Solved

Aggregate grouping rollup sum

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. ...
Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    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])),[%])
    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.

Anonymous's avatar
Anonymous
Not applicable
4 years ago

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.

  • rtech2022's avatar
    rtech2022
    Regular Visitor
    4 years ago

    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