How to find the most frequent value (through blanks as well)

[tomislav_mi]

Hey guys,

Can you please advise:

I have a table that looks like this and I would need to get the column in orange - the most frequent revenue value for Customer (disregarding blank values.)

Customer	Account	Revenue	Most Frequent Value
A	aa	150000	298700
A	bb	298700	298700
A	cc	0	298700
A	dd	298700	298700
A	ee	298700	298700
A	ff	300	298700
B	F34	(blank)	250
B	T45	250	250

Hope this could be solved somehow?

Many thanks!

[AntrikshSharma]

@tomislav_mi For most recent value there should be a date associated with it, do you have it? Otherwise a solution can still be prepared but it will break if the logic doesn't fits later.

[tomislav_mi]

Hey,

thank you for your response.

Not most recent but most frequent value so there is no date in this table.

[v-yiruan-msft · Accepted solution]

Hi @tomislav_mi ,

You can create two measures as below:

Count of revenue = 
CALCULATE (
    COUNT ( 'Revenue'[Revenue] ),
    ALLEXCEPT ( 'Revenue', 'Revenue'[Customer], Revenue[Revenue] )
)

Most Frequent Value = 
VAR _max =
    MAXX ( ALLEXCEPT ( 'Revenue', 'Revenue'[Customer] ), [Count of revenue] )
RETURN
    CALCULATE (
        MAX ( 'Revenue'[Revenue] ),
        FILTER (
            ALL ( 'Revenue' ),
            'Revenue'[Customer] = MAX ( 'Revenue'[Customer] )
                && [Count of revenue] = _max
        )
    )

Best Regards
Community Support Team _ Rena Ruan
If this post helps, then please consider Accept it as the solution to help the other members find it more.