cancel
Showing results for
Did you mean: Frequent Visitor

## Calculate the most common cost

Greetings, 1 ACCEPTED SOLUTION  Solution Specialist

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.

9 REPLIES 9  Solution Specialist

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.  Helper I

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') Frequent Visitor
@GuillemXII, you missed a closing parenthesis:
ALL('Datos Numericos'[1.PST Compromiso]  Helper I

A silly mistake on my part... It works fine now! Thank you! Frequent Visitor
@GuillemXII, you missed a closing parenthesis:
ALL('Datos Numericos'[1.PST Compromiso] Frequent Visitor

@erik_tarnvik, great thanks. That helped me a lot.  Community Champion

Hi @dmytro_poUse this measure

```CommonCost :=
CALCULATE (
VALUES ( Table1[Cost] ),
FILTER (
Table1,
MAXX ( Table1, CALCULATE ( COUNT ( Table1[Cost] ) ) )
= CALCULATE ( COUNT ( Table1[Cost] ) )
)
)```
Regards
Zubair Frequent Visitor

Greetings!

Could you please help to creare a measure, which would calculate the most comon cost for each product.   Super User

One way to do this:

For this Enter Data query:

```let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCijKTylNLlEwVNJRMlWK1UEVMcMQMSdZxAgoYkKWiCmGiCGKiDFWESMMEWMMEVMi1GAViQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Product = _t, Cost = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Product", type text}, {"Cost", Int64.Type}})
in
#"Changed Type"```

You can create this measure:

```Mode = IF(
CALCULATE(
MAXX(
VALUES(ProductCosts[Cost]),
CALCULATE(
COUNTROWS(ProductCosts)
)
),
ALLSELECTED(ProductCosts)
)
= COUNTROWS(ProductCosts),
"MODE",
BLANK()
)```

Check out my External Tool for Power BI Desktop!
Microsoft Hates Greg's Quick Measures
Check out my latest book!    