Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
dmytro_po
Frequent Visitor

Calculate the most common cost

 

Greetings,

please help to write a measure that calculates the most common cost for each product.

 

Most commom cost.jpg

1 ACCEPTED SOLUTION
erik_tarnvik
Solution Specialist
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.

 

View solution in original post

9 REPLIES 9
erik_tarnvik
Solution Specialist
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.

 

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')

 

 

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

A silly mistake on my part... It works fine now! Thank you!

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

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

Zubair_Muhammad
Community Champion
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

Please try my custom visuals
dmytro_po
Frequent Visitor

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.

 

Most commom cost.jpg

 

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()
 )

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.