Return measure of value with most repeated values in a column

[BenGWeeks]

Hi there,

How do I create a calculated field that returns the most frequent values from another table?

This is so I can have KPIs (cards) for each row in Table 2. Many thanks.
Many thanks.
Ben

[Anonymous · Accepted solution]

Hi @BenGWeeks ,

Please follow these steps:

1.Select Item column and "unpivot other columns"

2.Add count column of each value of each attribute :

count =
CALCULATE (
    COUNTROWS ( 'Table1 (2)' ),
    ALLEXCEPT ( 'Table1 (2)', 'Table1 (2)'[Attribute], 'Table1 (2)'[Value] )
)

 3. Add rank column:

rank =
VAR a = [Attribute]
VAR t1 =
    FILTER ( ALL ( 'Table1 (2)' ), 'Table1 (2)'[Attribute] = a )
RETURN
    RANKX ( t1, RANKX ( t1, [count],, DESC, DENSE ),, ASC, DENSE )

4. Create a new table:

New Table =
ADDCOLUMNS (
    DISTINCT ( 'Table1 (2)'[Attribute] ),
    "Value(most popular)", LOOKUPVALUE ( 'Table1 (2)'[Value], [rank], 1, [Attribute], [Attribute] )
)

The final output is shown below:

Here is the pbix file .

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[timg]

Hi Ben,

Is this perhaps what you are looking for? (in my example I am taking the product category which has the most revenue. In your case you could replace the revenue measure with a COUNT() of the value to get the value with the highest count )

MaxCategory =
CALCULATE (
    MAX ( DimProduct[Category] ),
    FILTER (
        VALUES ( DimProduct[Category] ),
        RANKX ( ALL ( DimProduct[Category] ), FactSales[Revenue],, DESC ) = 1
    )
)

 

Best regards,

Tim

[BenGWeeks]

Not sure how to get this to work in terms of Table 1 and Table 2?