The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event! Join us in Stockholm, Sweden from September 24-27, 2024.
2-for-1 sale on June 20 only!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
Greetings,
please help to write a measure that calculates the most common cost for each product.
Solved! Go to Solution.
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.
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.
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')
A silly mistake on my part... It works fine now! Thank you!
Hi @dmytro_poUse this measure
CommonCost := CALCULATE ( VALUES ( Table1[Cost] ), FILTER ( Table1, MAXX ( Table1, CALCULATE ( COUNT ( Table1[Cost] ) ) ) = CALCULATE ( COUNT ( Table1[Cost] ) ) ) )
Greetings!
Could you please help to creare a measure, which would calculate the most comon cost for each product.
Thank you in advance for your help.
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() )
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
104 | |
97 | |
80 | |
62 | |
57 |
User | Count |
---|---|
264 | |
120 | |
115 | |
83 | |
70 |