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 ,

 

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.

  • rtech2022's avatar
    rtech2022
    Regular Visitor
    4 years ago

    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] ))