Forum Discussion

Renna's avatar
Renna
Frequent Visitor
2 years ago
Solved

Matrix Row Subtotal for Text as the most frequent values, instead of "First"

I have read many posts that mention similar topics, but they don't necessarily cover what I'm looking for. Usually, for text values in the Matrix, we only have options to display them as 'First', 'Last', or 'Count'. However, in this case, I want the row subtotal to show the most frequent values. If there are ties, it should display the values with the highest number in the subsequent values. Is there anyway to fix this behavior?
Link to the pbix with sample data

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi Renna ,

    You can create a measure as below and replace the field [Project Manager] with this new measure.

    MProjectManager =
VAR MaxCount =
    MAXX (
        VALUES ( 'Table'[Project Manager] ),
        CALCULATE ( COUNT ( 'Table'[Project Manager] ) )
    )
VAR PMList =
    CONCATENATEX (
        FILTER (
            VALUES ( 'Table'[Project Manager] ),
            CALCULATE ( COUNT ( 'Table'[Project Manager] ) ) = MaxCount
        ),
        'Table'[Project Manager],
        ", "
    )
VAR ProjectTypeTable =
    SUMMARIZE (
        'Table',
        'Table'[Project Types],
        'Table'[Project ID],
        'Table'[Project Manager]
    )
VAR ModeValue =
    TOPN (
        1,
        ADDCOLUMNS (
            ProjectTypeTable,
            "SRevenue",
                CALCULATE (
                    SUM ( 'Table'[Revenue] ),
                    ALLEXCEPT (
                        'Table',
                        'Table'[Project Manager],
                        'Table'[Project ID],
                        'Table'[Project Types]
                    )
                )
        ),
        [SRevenue], DESC
    )
RETURN
    IF (
        IFERROR ( SEARCH ( ",", PMList, 1, 0 ), 0 ) > 0,
        MAXX ( ModeValue, [Project Manager] ),
        PMList
    )

    Best Regards

9 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Renna ,

    You can create a measure as below and replace the field [Project Manager] with this new measure.

    MProjectManager =
VAR MaxCount =
    MAXX (
        VALUES ( 'Table'[Project Manager] ),
        CALCULATE ( COUNT ( 'Table'[Project Manager] ) )
    )
VAR PMList =
    CONCATENATEX (
        FILTER (
            VALUES ( 'Table'[Project Manager] ),
            CALCULATE ( COUNT ( 'Table'[Project Manager] ) ) = MaxCount
        ),
        'Table'[Project Manager],
        ", "
    )
VAR ProjectTypeTable =
    SUMMARIZE (
        'Table',
        'Table'[Project Types],
        'Table'[Project ID],
        'Table'[Project Manager]
    )
VAR ModeValue =
    TOPN (
        1,
        ADDCOLUMNS (
            ProjectTypeTable,
            "SRevenue",
                CALCULATE (
                    SUM ( 'Table'[Revenue] ),
                    ALLEXCEPT (
                        'Table',
                        'Table'[Project Manager],
                        'Table'[Project ID],
                        'Table'[Project Types]
                    )
                )
        ),
        [SRevenue], DESC
    )
RETURN
    IF (
        IFERROR ( SEARCH ( ",", PMList, 1, 0 ), 0 ) > 0,
        MAXX ( ModeValue, [Project Manager] ),
        PMList
    )

    Best Regards

    • Renna's avatar
      Renna
      Frequent Visitor
      2 years ago

      It works perfectly. Thank you so much!

    • Renna's avatar
      Renna
      Frequent Visitor
      2 years ago

      Thank you very much Ritaf1983 . I just uploaded and shared the link to the pbix file with sample data. Appreciated helps on this problem.