Number of cases equal to the maximum and minimum value

Question

I have a list consisting of Item, Price and Date. My objective is to figure out:

• How many records for each item
• How many different prices
• How many prices are equal to the max value of each item

Calculating the number of records for each item item is easy as calculating the number of different values. I haven’t been able to solve the number of item equal to the maximum or minimum value. I haven’t figured out how I can use the result of a measure to filter the records I want. If I put the value manually in the measure it will give the right result (number of records equal to the value), but I need this to be dynamic. Also I want to be able to slice data by date otherwise I could do an approach by calculated column.

Can anyone help me figure this out?

Answer 1

Hello Pedro,

   You have not specified in which you need to do this calculation  SSRS or Power Pivot or in your cube ?  Please provide more details for further assistance.

---

Best Regards Sorna

Answer 2

Hello Sorna,

I'm working with Powerpivot running on Excel 2010.

Hope you can help,

Pedro

Answer 3

Hello Pedro ,

   I couldnt find a way to use a measure in another measure. But this can be achived by this workaround approach.

  For the below , i have created a table test with (Item , Date & Price columns)

   1. Create a caluclated column "MaxVal" with below expression

=maxx(filter(test, EARLIER(test[Item]) = test[Item] ),test[Price])

   2. Create a measure with below expression

Measure 2:=countx(filter(test,test[Price] = test[MaxVal]),test[Price])

 

---

Best Regards Sorna

Answer 4

Hello Sorna,

The calculated column approach is something I said I wanted to avoid because it will not let me slice data by date. I need a
solution that can analyze max and min values in the current filter context. <o:p></o:p>

Let’s see if anyone can shed some light on how this can be achieved or if the answer is that it is not possible to have a measure use a value of another measure as a filter.