Counts of Pivoted Data (as a column)

[Anonymous]

Hi!

I've been reading through other solutions and nothing seems to work. I am trying to create a column that will return counts of a specfic column/value after I've pivoted the data. A simplifed data sample is below:

I want to return a column that returns the ranking of each attribute based on the count of values. For example, if we look at the attribute "App", the value of "A" appears in 2 out of 3 instances. It would therefore be ranked 1 because it has the highest number of occurrences. I would prefer that the solution is a column rather than a measure, since I will be using the ranking as a slicer.

PS - the solution doesn't have to use a switch statement, that was just a thought…

Thank you!

[Anonymous]

Thank you so much! Extremely helpful!

[v-piga-msft · Accepted solution]

Hi @Anonymous ,

There are two ways you could achieve that.

1. You need to create two calculated columns like below.

Column = CALCULATE(COUNT('Table'[value]),ALLEXCEPT('Table','Table'[Attribute],'Table'[value]))

rank =
RANKX (
    FILTER ( 'Table', 'Table'[Attribute] = EARLIER ( 'Table'[Attribute] ) ),
    'Table'[Column],
    ,
    DESC,
    DENSE
)

2. You could just create one calculated column with the dax below.

rank_ =
VAR t =
    SUMMARIZE (
        FILTER ( 'Table', [Attribute] = EARLIER ( 'Table'[Attribute] ) ),
        [Attribute],
        'Table'[value],
        "C", COUNT ( 'Table'[value] )
    )
VAR t2 =
    ADDCOLUMNS (
        t,
        "Rank", RANKX ( FILTER ( t, [Attribute] = EARLIER ( 'Table'[Attribute] ) ), [C],, DESC )
    )
RETURN
    MAXX (
        FILTER (
            t2,
            [Attribute] = EARLIER ( [Attribute] )
                && [value] = EARLIER ( 'Table'[value] )
        ),
        [Rank]
    )

Here is the output.

Hope this can help you!

Best Regards,

Cherry