Forum Discussion

Operacoes's avatar
Operacoes
Regular Visitor
4 years ago

Handle latest values ​​by group

Hi guys,

I needed to sum up the most recent values ​​per person, I found the last date function which resolves that part of the recent value. But it only takes the most recent value from everyone, how do I segment this by group?

I'm putting a print of an example table and when trying to get the most recent value if the category is radar it returns 8 (which is not wrong)

But I wanted it to return 16, most recent 8 from one person with the most recent 8 from another for radar category.

 

 

 

7 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    4 years ago

    Operacoes , Try a measure like

     

    Measure =
    VAR __id = MAX ('Table'[pessoa] )
    VAR __date = CALCULATE ( MAX('Table'[Data] ), ALLSELECTED ('Table' ), 'Table'[pessoa] = __id )
    CALCULATE (Sum(Table[Nota]), VALUES ('Table'[pessoa] ),'Table'[pessoa] = __id,'Table'[Data] = __date )

    • Operacoes's avatar
      Operacoes
      Regular Visitor
      4 years ago

      Hi, thanks for help but didn't solve yet

       

      Max('Table'[pessoa]) will return Mateus, right? but I need the most recent answers for all pairs (Pessoa (subject) and Ferramenta (Tool))

      In other words, when I work on tool "Radar" I need to sum the most recent grade for every subject in this tool, am i clear?

  • johnt75's avatar
    johnt75
    Icon for Super User rankSuper User
    4 years ago

    The below will give the sum of the latest values for each category

    most recent = SUMX(
    ADDCOLUMNS( SUMMARIZE( 'Table', 'Table'[Pessoa], 'Table'[Ferramenta]), "@recent val",
    SELECTCOLUMNS( CALCULATETABLE( TOPN( 1, 'Table', 'Table'[Data], desc) ), "@val", 'Table'[Nota]) 
    ),
    [@recent val]
    )
    • Operacoes's avatar
      Operacoes
      Regular Visitor
      4 years ago

      It's a start, i tried to simplify but my real problems is to count if the recent grade by tool is in one of the ranges: 

       

      0 to 6

      7 to 8

      and 9 to 10

       

      For my table in example, to tool Radar we have 1 in 7 to 8 (most recent for Du) and another 1 in 7 to 8 (most recent for Mateus)... to tool Propósito we have 1 in 0 to 6 (most recent for Du) and another 1 in 9 to 10 (most recent for Mateus)

       

      Do you think it is possible?

      • johnt75's avatar
        johnt75
        Icon for Super User rankSuper User
        4 years ago

        I think you would need separate measures for each grouping, but they would give values down to the ferramenta level. e.g.

        Most recent 1-7 =
        var summaryTable = ADDCOLUMNS( 
           SUMMARIZE( 'Table', 'Table'[Pessoa], 'Table'[Ferramenta]), "@recent val",        
           SELECTCOLUMNS( CALCULATETABLE( TOPN( 1, 'Table', 'Table'[Data], desc) ), "@val", 'Table'[Nota])  
        )
        var result = COUNTROWS( FILTER( summaryTable, [@recent val] >= 1 && [@recent val] <= 7 ) )

        The summaryTable variable would be the same in all measures, you'd just need to change the boundaries