Forum Discussion

Fabiopb's avatar
Fabiopb
Frequent Visitor
4 years ago
Solved

Calculate a Max value for differente matriculates

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 dia...
Need Help
  • v-jingzhang's avatar
    v-jingzhang
    4 years ago

    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.

     

v-jingzhang's avatar
v-jingzhang
Icon for Community Support rankCommunity Support
4 years ago

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's avatar
Fabiopb
Frequent Visitor
4 years ago

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's avatar
    v-jingzhang
    Icon for Community Support rankCommunity Support
    4 years ago

    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.