none
Color expression possible based on sales amount > top 5?

    Question

  • Hi all,

    I need an expression that colors my [Sales amount] green when the [Sales amount] is higher than the 5th item in DESC order. (So sales amount must be higher than the 5th best). Or in other words, I need to color the top 5 ...

    I can make the MDX query no problem

    head(
    order(
    [Product].[Category].[Category]
    *[Product].[Subcategory].[Subcategory]
    *[Product].[Product].[Product]
    ,[measures].[sales amount], DESC
     )  ,5)
      .item(4)

    The problem is, I don't rly know how to do this in an expression, how can I say iff( sales amount > 5th best item)?

    I can't really put this measure in a column/row in my dataset because than it gives an error so :/

    Any suggestion?


    • Edited by Yvanlathem Saturday, July 26, 2014 6:43 PM
    Saturday, July 26, 2014 6:42 PM

Answers

  • Hi Yvanlathem,

    According to your description, you want to highlight the tops cells in the results, right?

    In this case, you can create a calculation to return the 5th value, and then use it in IIF function. I have tested it on my local environment, the query below is for you reference.

    WITH MEMBER [Measures].[5thValue]
    AS
    AGGREGATE(BOTTOMCOUNT(TOPCOUNT([Date].[Calendar].[Date].&[20070207]:[Date].[Calendar].[Date].&[20070214],5,[Measures].[Internet Sales Amount]),1,[Measures].[Internet Sales Amount]),[Measures].[Internet Sales Amount])
    MEMBER MEASURES.CELLPROPERTYDEMO AS [Measures].[Internet Sales Amount]
    , FORE_COLOR=RGB(0,0,255)
    , BACK_COLOR=IIF([Measures].[Internet Sales Amount]>=[Measures].[5thValue], RGB(255,0,0), RGB(0,255,0))
    , FONT_SIZE=10
    , FORMAT_STRING='#,#.000'
    SELECT {MEASURES.CELLPROPERTYDEMO}ON 0,
    {[Date].[Calendar].[Date].MEMBERS} ON 1
    FROM (
    SELECT {[Date].[Calendar].[Date].&[20070207]:[Date].[Calendar].[Date].&[20070214]} ON 0
    FROM
    [Adventure Works]
    )
    CELL PROPERTIES VALUE, FORMATTED_VALUE, FORE_COLOR, BACK_COLOR, FONT_SIZE

    Reference
    http://msdn.microsoft.com/en-IN/library/ms145573.aspx

    Regards,


    Charlie Liao
    TechNet Community Support

    Monday, July 28, 2014 7:20 AM
    Moderator