topic Re: Most common value from a measure in DAX Commands and Tips

Most common value from a measure

Clay82 — Mon, 15 Nov 2021 18:46:50 GMT

Hello!

Say I have a table with two number columns (A and B).

I have created a measure (not a calculated column) (C) that calculates the differense between the columns (A - B)

A B C
5 3 2
7 2 5
6 4 2
4 2 2

Now I would like to show the most common value (which in this case is 2) in a card viz based on the measure I've created.

How can I create such a measure?

Thanks!

Re: Most common value from a measure

Jihwan_Kim — Mon, 15 Nov 2021 19:15:16 GMT

Hi,

Please check the below picture and the attached pbix file.

All measures are in the attached pbix file.

The most common value: =

VAR add_measureC =

ADDCOLUMNS ( Data, "@measureC", [C:] )

VAR groupby_measureC =

GROUPBY (

add_measureC,

[@measureC],

"@count_rows", SUMX ( CURRENTGROUP (), 1 )

)

VAR max_countrows =

MAXX ( groupby_measureC, [@count_rows] )

RETURN

MAXX ( FILTER ( groupby_measureC, [@count_rows] = max_countrows ), [@measureC] )

Re: Most common value from a measure

smpa01 — Mon, 15 Nov 2021 19:24:37 GMT

Clay82 you can achieve the end goal with thfollowing measure

_diff = SUM(tbl[A])-SUM(tbl[B]) Measure = VAR _1 = ADDCOLUMNS ( tbl, "diff", [_diff] ) VAR _2 = ADDCOLUMNS ( _1, "count", COUNTX ( FILTER ( _1, EARLIER ( [diff] ) = [diff] ), [diff] ) ) VAR _3 = ADDCOLUMNS ( _2, "rank", RANKX ( FILTER ( _2, EARLIER ( [count] ) < [count] ), [count],, DESC, DENSE ) ) RETURN MAXX ( FILTER ( _3, [rank] = 1 ), [diff] )

Re: Most common value from a measure

CNENFRNL — Tue, 16 Nov 2021 04:02:31 GMT

Simple enough by Excel worksheet formula.

Re: Most common value from a measure

Clay82 — Tue, 16 Nov 2021 05:48:04 GMT

Wow Jihwan it worked perfectly! Thank you!

Also I need the same for column B (which is in fact a measure based on a calculated column. So another measure to find the most common value in column B. Is it possible for you to help me?

Re: Most common value from a measure

Jihwan_Kim — Tue, 16 Nov 2021 12:38:17 GMT

Hi,
I am not sure if I understood your question correctly, but please check the below picture and the attached pbix file.

The most common value from Column B: =
VAR groupby_columnB =
GROUPBY ( Data, Data[B], "@count_rows", SUMX ( CURRENTGROUP (), 1 ) )
VAR max_countrows =
MAXX ( groupby_columnB, [@count_rows] )
RETURN
MAXX ( FILTER ( groupby_columnB, [@count_rows] = max_countrows ), Data[B] )