Forum Discussion

javedbh's avatar
javedbh
Icon for Helper II rankHelper II
10 years ago

calculate minimum and maximum percentage

How do I calculate minimum and maximum percentage?

 

Use the following table structure:

Item Name, Pass Dies, Total Dies

 

Percentage = Pass Dies / Total Dies x 100

 

Any help would be appreciated. Thanks

Need Help
Show and Tell

4 Replies

  • austinsense's avatar
    austinsense
    Icon for Impactful Individual rankImpactful Individual
    10 years ago

    I think what you're asking is "How do I calculate the min and max percentage across the items?".  Answering this question requires doing a calculation for every item and then bringing back the final result - for example, calculate the percentage for each product and then tell me which percentage is the lowest?  Fortunately this isn't super hard if you understand the "X" functions.

     

    Here's what you're looking for ...

    MinItem% = MINX( ALL( Items ), Percentage )

MaxItem% = MAXX( ALL( Items ), Percentage )

     

     

    The "X" functions take a table as the first argument, and the function is going to do a calculation for every row in that table.  In this case we want to do a calculation for the items.  I included the ALL function to force the calculation to look at ALL the items in the table (ignoring any filtering that's going on).  The second argument in the "X" function is the expression you want to evaluate for each row - in this case, %.

     

    Maybe you don't want to calculate the min/max based on the items, if that's the case just replace the items table with the table that you want to iterate over.

     

    Hope this helps,

    Austin

    • javedbh's avatar
      javedbh
      Icon for Helper II rankHelper II
      10 years ago

      austinsense Thanks for the reply but I'm not sure how to implement it.

       

      Here is my data:

       

      Item NamePass DiesTotal DiesDate Added
      Item A507028-Jan-16
      Item A709012-Feb-16
      Item A11514515-Feb-16
      Item A23030027-Feb-16
      Item A185010-Mar-16
      Item A6710017-Mar-16
      Item B6815012-Feb-16
      Item B11920027-Feb-16
      Item B26840017-Mar-16
      Item B742100028-Mar-16
      Item C548028-Jan-16
      Item C7510012-Feb-16
      Item C12815028-Mar-16
      Item C347029-Mar-16
      Item C791008-Apr-16

       

      I want two things:

      1. Overall min and max percentage
      2. Item-wise min and max percentage

      I creaed following measures:

      Percentage = DIVIDE(SUM(ItemDetails[Pass Dies]), SUM(ItemDetails[Total Dies]), 0) * 100
      Max 1 = MAXX( ALL( ItemDetails ), [Percentage] )
      Max 2 = MAXX( ALL( ItemDetails ), DIVIDE(SUM(ItemDetails[Pass Dies]), SUM(ItemDetails[Total Dies]), 0) * 100 )
      Max 3 = MAXX( ALL( ItemDetails[Item Name]), [Percentage] )
      Max 4 = MAXX( ALL( ItemDetails[Item Name]), DIVIDE(SUM(ItemDetails[Pass Dies]), SUM(ItemDetails[Total Dies]), 0) * 100

       

      Measure "Max 1" gives the correct overall max percentage. But all other results are incorrect. BTW can you explain how these measures (Max 1,2,3,4) are working?

       

      A screenshot is attached that shows how I configured the chart:

       

       

      Sorry for any inconvenience. Thanks.

       

      • austinsense's avatar
        austinsense
        Icon for Impactful Individual rankImpactful Individual
        10 years ago

        You're off to a great start! Two things ...

         

        1. You need to use the query editor to create an items table that has one row for every item

        • Duplicate the Item Details Query
        • Remove all columns except the Item Name
        • Remove Duplicates on the Item Name
        • Load to Data Model & Create a Relationship between the new "Item" table and the "Item Details" table
        • Replace Item Details with Item in your formulas (you'll only need formulas 1 & 2)

        2. Write your "DIVIDE(SUM ..." measure separately and then reference it in the MAX measure.

         

        I'm tied up all day but I'm sure you can figure this out with the help of other people on the forum and the internet.