Forum Discussion
Calculate the most common cost
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.
Solution SpecialistTry 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.
erik_tarnvik, great thanks. That helped me a lot.
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')
