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

    Wednesday, March 06, 2013 9:47 AM

Answers

  • 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

    Thursday, March 07, 2013 10:44 AM

All replies

  • 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

    Wednesday, March 06, 2013 1:52 PM
  • Hello Sorna,

    I'm working with Powerpivot running on Excel 2010.

    Hope you can help,

    Pedro

    Wednesday, March 06, 2013 5:16 PM
  • 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

    Thursday, March 07, 2013 10:44 AM
  • 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.


    Thursday, March 07, 2013 9:37 PM