Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

sum a measure with grouping

Hi!

Just wondering if this is possible.  

I am attempting to sum the Category Rank Pts by Employee.
Category Rank Pts is a measure.

The end result:

 

 

Need Help

7 Replies

  • aj1973's avatar
    aj1973
    Icon for Community Champion rankCommunity Champion
    5 years ago

    Hi Anonymous 

    use this measure

     

    Category Rank Final_BIS =

    VAR _EmployeeID = SELECTEDVALUE('TABLE'[Employee])

    VAR _Result = CALCULATE(SUM('TABLE'[Category Rank Final]) , 'TABLE'[Employee] = _EmployeeID)

    RETURN

    _Result

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hi.

      I was looking for the formula for the Category Rank Final as a sum of the Category Rank Pts measures by employee.
      I added the Category Rank Pts measure in the thread.  Thanks.

  • v-easonf-msft's avatar
    v-easonf-msft
    Icon for Community Support rankCommunity Support
    5 years ago

    Hi,  Anonymous 

    Could you please tell me whether your problem has been solved?
    You can also take a try formula as below:

    Measure_category rank final =
VAR tab =
    ADDCOLUMNS (
        SUMMARIZE ( 'Table', 'Table'[Appr Header], 'Table'[Employee] ),
        "Category Rank final", [Category Rank final]
    )
RETURN
    SUMX ( tab, [Category Rank final] )

    or 

    Measure_category rank final = SUMX(VALUES('Table'[Appr Header]),[Category Rank final])

     

    If it doesn't meet your requirement,please share your formula of "Category Rank Pts " for further research.

     

    Best Regards,
    Community Support Team _ Eason

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago
      Hi. 
      The Category Rank Pts measure below.  
      I am still having trouble summing up measures.
      I tried using AddColumn to store the Category Rank Pts, but still unable to sum them up by employee.
       
      Category Rank Pts =
      VAR currentSubCat = max('Table'[Appr Header])

       

      Return
      SWITCH(
      currentSubCat
      , "PP"
      , RANKX( FILTER( ALL( 'Table' ), 'Table'[Appr Header] = currentSubCat ), CALCULATE( sum('Table'[sumQty]) ),,ASC )*0.6
      , "PA"
      , RANKX( FILTER( ALL( 'Table' ), 'Table'[Appr Header] = currentSubCat ), CALCULATE( sum('Table'[sumQty]) ),,ASC )*0.2
      , "INS"
      , RANKX( FILTER( ALL( 'Table' ), 'Table'[Appr Header] = currentSubCat ), CALCULATE( sum('Table'[sumQty]) ),,ASC )*0.1
      , "PT"
      , RANKX( FILTER( ALL( 'Table' ), 'Table'[Appr Header] = currentSubCat ), CALCULATE( sum('Table'[sumQty]) ),,ASC )*0.1
      )
       
      Thanks!