Forum Discussion

dmytro_po

Frequent Visitor

9 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
9 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.

Anonymous

Not applicable

8 years ago

Sorry, but when I introduce the code, the equivalent for [NoRows] gets underlained in red and tells me that "Argument '3' in ALL function is required'. Which could be the error here?:

1. M.PST = 
FIRSTNONBLANK(
    TOPN(
        1;
        VALUES('Datos Numericos'[1.PST Compromiso]);
        RANKX(ALL('Datos Numericos'[1.PST Compromiso];[Nº Filas];;ASC)
     );
     1        
 )

Where [Nº Filas] = COUNTROWS('Datos Numericos')

dmytro_po

Frequent Visitor

8 years ago

Anonymous, you missed a closing parenthesis:
ALL('Datos Numericos'[1.PST Compromiso]