Forum Discussion

dmytro_po's avatar
dmytro_po
Frequent Visitor
8 years ago
Solved

Calculate the most common cost

  Greetings, please help to write a measure that calculates the most common cost for each product.  
  • erik_tarnvik's avatar
    8 years ago

    Try this:

     

    First a measure to count rows.

     

     

    NoRows = COUNTROWS(Data)

    Then figure out the most common cost. The FIRSTNONBLANK deals with ties, without it you will get an error in the precense of the two (or more) cost values being most common.

     

     

     

    MostCommonCost = 
    FIRSTNONBLANK(
        TOPN(
            1, 
            VALUES(Data[Cost]), 
            RANKX(ALL(Data[Cost]),[NoRows],,ASC)
        ), 
        1
    )

    That should do it.