Forum Discussion

Drobb's avatar
Drobb
Frequent Visitor
2 years ago
Solved

Max value filtered and grouped

Hi, 

I have a moderately specific question that I have been struggling with for some time.  

I have a table called "performance" that has the columns: ID; Sitename; Date; NumberofTransactions.  The 'performance'[Date] column is connected with a many to one relationship to a general 'calendar'[Date] table being used to filter the data. 

 

I am trying to return the max NumberofTransactions 1 ID has had for each Sitename, filterable by the date. 

 

So for example, if I had the data set below where ID-Sitename-Date-NumberofTransactions: 

AB-Ancorage-1/1/23-16

AB-Ancorage-1/2/23-18

AB-Ancorage-1/3/23-19

AC-Ancorage-1/2/23-2

AC-Ancorage-1/3/23-29

DA-Dallas-1/2/23-19

DA-Dallas-1/5/23-20

DB-Dallas-1/2/23-17

 

The Resulting Table would look like this: ID-Site-NumberofTransactions-MaxNumberofTransactionsofSite  when filtered on the dates 1/2/23-1/3/23: 

AB-Ancorage-37-37

AC-Ancorage-31-37

DA-Dallas-19-19

DB-Dallas-17-19

 

The hardest link for me has been to get it to be filterable by date.   Any support is appreciated.

 

  • Dangar332's avatar
    Dangar332
    2 years ago

    hi, Drobb 

    try below measure

     

    max no of side = 
var a =ADDCOLUMNS(ALL(performance[id],performance[sitename]),"sum",CALCULATE(SUM(performance[no of tran]),performance[sitename]=MAX(performance[sitename])))
return
MAXX(FILTER(a,performance[sitename]=MAX(performance[sitename])),[sum])

     

     

  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi Drobb ,

     

    According to your description, here are my steps you can follow as a solution.

    (1) My test data is the same as yours.

    (2) We can create a measure. 

    var _table =ADDCOLUMNS(SUMMARIZE(ALL('performance'),[ID],[Sitename]),"sum",CALCULATE(SUM(performance[NumberofTransactions]),performance[sitename]=MAX(performance[sitename])))
return
MAXX(FILTER(_table,[sitename] in VALUES('performance'[Sitename])),[sum])

    (3) Then the result is as follows.

    Best Regards,

    Neeko Tang

    If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

2 Replies

  • Dangar332's avatar
    Dangar332
    Icon for Resident Rockstar rankResident Rockstar
    2 years ago

    hi, Drobb 

    try below measure

     

    max no of side = 
var a =ADDCOLUMNS(ALL(performance[id],performance[sitename]),"sum",CALCULATE(SUM(performance[no of tran]),performance[sitename]=MAX(performance[sitename])))
return
MAXX(FILTER(a,performance[sitename]=MAX(performance[sitename])),[sum])

     

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Drobb ,

     

    According to your description, here are my steps you can follow as a solution.

    (1) My test data is the same as yours.

    (2) We can create a measure. 

    var _table =ADDCOLUMNS(SUMMARIZE(ALL('performance'),[ID],[Sitename]),"sum",CALCULATE(SUM(performance[NumberofTransactions]),performance[sitename]=MAX(performance[sitename])))
return
MAXX(FILTER(_table,[sitename] in VALUES('performance'[Sitename])),[sum])

    (3) Then the result is as follows.

    Best Regards,

    Neeko Tang

    If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly.