Calculate a Max value for differente matriculates

[Fabiopb]

Hello!

I have a table with different values of diameter for different matricules. I need to calculate which is the max diameter for each matricule and after that calculate how many times that diameter appears for that matricule. How could I do it with a calculated column and/or a meassure? I thougth of creating a second table where I have a column with unique matricules values then create a second column with = Calculate(MAX(Diameter[Table1]), Matricules[Table1] = Matricules[Table2]) and a third column = CALCULATE(COUNT(Diameter[Table1]), Matricules[Table1] = Matricules[Table2],Diameter[Table1] = MAX(Diameter[Table1]). That was my idea, but I don´t know how to write that. Could you help me please? I am a rookie 

[v-jingzhang]

Hi @Fabiopb 

Here is one solution with calculated columns. Create two columns

Matricule Max Diameter = CALCULATE(MAX('Matricules'[Diameter]),ALLEXCEPT(Matricules,Matricules[Matricule]))

Max Count = CALCULATE(COUNT(Matricules[Matricule]),ALLEXCEPT(Matricules,Matricules[Matricule]),Matricules[Diameter]=Matricules[Matricule Max Diameter])

And here is one solution with a measure based on above sample data. 

Measure = 
var matricule_max_diameter = MAX(Matricules[Diameter])
return
CALCULATE(COUNT(Matricules[Matricule]),Matricules[Diameter]=matricule_max_diameter)

You can choose either per your need. 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[Fabiopb]

Hello,

Thank you for the response. I am actually looking for a solution like this:

I would prefer to calculate the new columns in a new table with the matricules not repeated. I am looking for a solution like that because the main table has a lot of rows which I believe including new calculated columns would be worse for the structure. If you could help with that I would appreciate it.

[v-jingzhang · Accepted solution]

Hi @Fabiopb 

Sorry for the late reply. You can first create a table with below code. This will return you a table with distinct matricules and their maximum diameter values. 

Table 2 = SUMMARIZE('Table','Table'[Matricule],"Max Diameter",MAX('Table'[Diameter]))

Then add a new column in above new table. 

Max Diameter Count = 
COUNTROWS (
    FILTER (
        'Table',
        'Table'[Matricule] = 'Table 2'[Matricule]
            && 'Table'[Diameter] = 'Table 2'[Max Diameter]
    )
)

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.