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] )
smpa01's avatar
smpa01
Icon for Community Champion rankCommunity Champion
4 years ago

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