calculate minimum and maximum percentage

[javedbh]

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

[austinsense]

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]

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

Here is my data:

Item Name	Pass Dies	Total Dies	Date Added
Item A	50	70	28-Jan-16
Item A	70	90	12-Feb-16
Item A	115	145	15-Feb-16
Item A	230	300	27-Feb-16
Item A	18	50	10-Mar-16
Item A	67	100	17-Mar-16
Item B	68	150	12-Feb-16
Item B	119	200	27-Feb-16
Item B	268	400	17-Mar-16
Item B	742	1000	28-Mar-16
Item C	54	80	28-Jan-16
Item C	75	100	12-Feb-16
Item C	128	150	28-Mar-16
Item C	34	70	29-Mar-16
Item C	79	100	8-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.

[v-sihou-msft]

@javedbh

In your scenario, when you use "ALL( ItemDetails )" in MAXX() function, it will ignore all filters for all columns. If you use "ALL( ItemDetails[Item Name])", it just ignore the filters on [Item Name].

Also, as @austinsense suggested, always put the calcuated measure expression separately, use the measure directly in aggregation function. Otherwise, each column will be evaluated for each row first, then do the calculation.

Reference:

ALL Function (DAX)

MAXX Function (DAX)

Regards,

[austinsense]

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.