Forum Discussion

Clay82's avatar
Clay82
Frequent Visitor
4 years ago
Solved

Most common value from a measure

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 ...
  • Jihwan_Kim's avatar
    Jihwan_Kim
    4 years ago

    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] )
Clay82's avatar
Clay82
Frequent Visitor
4 years ago

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? 

Jihwan_Kim's avatar
Jihwan_Kim
Icon for Super User rankSuper User
4 years ago

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] )