Forum Discussion

MTrullàs's avatar
MTrullàs
Helper III
4 years ago
Solved

Percentile

Hi everybody!

 

I want to calculate column D in this example with DAX.

 

 

I try to do it with this code, but I have some mistakes in the results.

 

 

Percentil Fábrica =
VAR Totalcoche =
[Dias Estancia Fábrica]
RETURN
IF(
HASONEVALUE(HIFA[Estancia Fábrica]),
COALESCE(
DIVIDE(
CALCULATE(
COUNTROWS(HIFA),
FILTER(
ALLEXCEPT(HIFA,HIFA[País]),
HIFA[Estancia Fábrica] < Totalcoche )
),
CALCULATE(
COUNTROWS(HIFA),
ALLEXCEPT(HIFA,HIFA[País])
)
),
0
)
)

 

Dias Estancia Fábrica = MAX(HIFA[Estancia Fábrica])

 

 

I don't know if you need more information to try help me. 

 

Thank you very much,

 

 

 

  • AlB's avatar
    AlB
    4 years ago

    MTrullàs 

    Ok. That is different from what I had understood earlier.  See it all at work in the attached file:

     

     

    NewMeasure = 
VAR total_ = CALCULATE ( COUNT ( Table1[Estancia Fábrica] ), ALL ( Table1 ) )
VAR currentEst_ = SELECTEDVALUE ( Table1[Estancia Fábrica], total_ )
VAR cumul_ =
    CALCULATE (
        COUNT ( Table1[Estancia Fábrica] ),
        Table1[Estancia Fábrica] <= currentEst_,
        ALL ( Table1 )
    )
RETURN
DIVIDE ( cumul_, total_ )

     

     

    		 Please accept the solution when done and consider giving a thumbs up if posts are helpful. 

    Contact me privately for support with any larger-scale BI needs, tutoring, etc.

     

12 Replies

  • AlB's avatar
    AlB
    Community Champion
    4 years ago

    Hi MTrullàs 

    I assume you're looking for a  calculated column. If so, create this and format as %:

    NewCol_ =
VAR total_ = CALCULATE ( SUM ( HIFA[Num_Coches] ), ALL ( HIFA ) )
VAR cumul_ =
    CALCULATE (
        SUM ( HIFA[Num_Coches] ),
        HIFA[Estancia Fábrica] <= EARLIER ( HIFA[Estancia Fábrica] ),
        ALL ( HIFA )
    )
RETURN
    DIVIDE ( cumul_, total_ )

    		Please accept the solution when done and consider giving a thumbs up if posts are helpful. 

    Contact me privately for support with any larger-scale BI needs, tutoring, etc.

     

    • MTrullàs's avatar
      MTrullàs
      Helper III
      4 years ago

      Hello,

       

      Thanks a lot for the quick answer,


      No, I'm lookin' for a metric.I need a measure, because I want to build a board like this:

      Before, I have a table with different rows, therefore, I made a cluster with the same "Estancia Fábrica", so I need a metric.

       

       

      Thank you again, for your help

      • AlB's avatar
        AlB
        Community Champion
        4 years ago

        MTrullàs 

        See if this measure works. It should be in a visual with HIFA[Estancia fábrica] on the rows, like the ones you show:

         

         

        NewMeasure_ =
VAR total_ = CALCULATE ( SUM ( HIFA[Num_Coches] ), ALL ( HIFA ) )
VAR currentEst_ = SELECTEDVALUE ( HIFA[Estancia Fábrica] )
VAR cumul_ =
    CALCULATE (
        SUM ( HIFA[Num_Coches] ),
        HIFA[Estancia Fábrica] <= currentEst_,
        ALL ( HIFA )
    )
RETURN
    DIVIDE ( cumul_, total_ )

         

         

        		 Please accept the solution when done and consider giving a thumbs up if posts are helpful. 

        Contact me privately for support with any larger-scale BI needs, tutoring, etc.

         

  • MTrullàs's avatar
    MTrullàs
    Helper III
    4 years ago

    Thank you... of course I do.
    I'm new to the forum, so I have no idea how I can share it.

    Sorry, I look forward to that.


    The significant column is "Estancia Fábrica"

     

    • AlB's avatar
      AlB
      Community Champion
      4 years ago

      MTrullàs 

      You have to share the URL to the file hosted elsewhere: Dropbox, Onedrive... or just upload the file to a site like tinyupload.com (no sign-up required).

       

      Be careful not to share confidential information

       

      		 Please accept the solution when done and consider giving a thumbs up if posts are helpful. 

      Contact me privately for support with any larger-scale BI needs, tutoring, etc.

       

  • AlB's avatar
    AlB
    Community Champion
    4 years ago

    MTrullàs 

    Ok. That is different from what I had understood earlier.  See it all at work in the attached file:

     

     

    NewMeasure = 
VAR total_ = CALCULATE ( COUNT ( Table1[Estancia Fábrica] ), ALL ( Table1 ) )
VAR currentEst_ = SELECTEDVALUE ( Table1[Estancia Fábrica], total_ )
VAR cumul_ =
    CALCULATE (
        COUNT ( Table1[Estancia Fábrica] ),
        Table1[Estancia Fábrica] <= currentEst_,
        ALL ( Table1 )
    )
RETURN
DIVIDE ( cumul_, total_ )

     

     

    		 Please accept the solution when done and consider giving a thumbs up if posts are helpful. 

    Contact me privately for support with any larger-scale BI needs, tutoring, etc.

     

    • MTrullàs's avatar
      MTrullàs
      Helper III
      4 years ago

      Thank you very much for your time. The solution is perfect!