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.

 

The desired output would look like this ..

Table relationship 

 

 

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

 

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.

8 Replies

  • 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

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

       

       

       

       

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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