Forum Discussion

OlegP's avatar
OlegP
New Member
7 years ago
Solved

Matrix Min&Max by different aggregation in the same table

I would appreciate a little help on the following topics

  1. Create Conditional Formatting for min & max values for each row for each aggregation (group by)
  2. Add new calc column at the end of the pivot table (for example STDV for each row) for each aggregation (group by)
  3. File Here
Need Help
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    7 years ago

    HI OlegP ,

    You can try to use following measure formula to replace original value field and enable conditional formatting on it.

    Measure =
IF (
    ISINSCOPE ( Sheet1[TypeString] ) && ISINSCOPE ( Sheet1[GroupNLPRuleName] )
        && ISINSCOPE ( Sheet1[NLPRuleName] ),
    COUNT ( Sheet1[ExpertOperationID] ),
    IF (
        COUNT ( Sheet1[ExpertOperationID] ) > 0,
        STDEVX.S (
            SUMMARIZE (
                Sheet1,
                [TypeString],
                [GroupNLPRuleName],
                [NLPRuleName],
                "Count", COUNT ( Sheet1[ExpertOperationID] )
            ),
            [Count]
        )
    )
)

    Regards,

    Xiaoxin Sheng

2 Replies

  • OlegP's avatar
    OlegP
    New Member
    7 years ago

    I would appreciate for help on the following topics

    1. Create Measure (for conditional formatting) for min & max values for each row for each aggregation (group by) in the matrix. 
    2. Add new calc column at the end of the matrix (for example STDV for each row) for each aggregation (group by)

    ExampleFile

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    HI OlegP ,

    You can try to use following measure formula to replace original value field and enable conditional formatting on it.

    Measure =
IF (
    ISINSCOPE ( Sheet1[TypeString] ) && ISINSCOPE ( Sheet1[GroupNLPRuleName] )
        && ISINSCOPE ( Sheet1[NLPRuleName] ),
    COUNT ( Sheet1[ExpertOperationID] ),
    IF (
        COUNT ( Sheet1[ExpertOperationID] ) > 0,
        STDEVX.S (
            SUMMARIZE (
                Sheet1,
                [TypeString],
                [GroupNLPRuleName],
                [NLPRuleName],
                "Count", COUNT ( Sheet1[ExpertOperationID] )
            ),
            [Count]
        )
    )
)

    Regards,

    Xiaoxin Sheng