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 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 

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.

     

3 Replies

  • 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.