Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Get Fabric Certified for FREE during Fabric Data Days. Don't miss your chance! Learn more

Reply
MTrullàs
Helper III
Helper III

Percentile

‎06-09-2022 09:55 PM

Hi everybody!

 

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

 

MTrulls_0-1654836204275.png

 

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

 

MTrulls_1-1654836452604.png

 

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

 

Thank you very much,

 

 

 

Message 1 of 13
1,437 Views
0
1 ACCEPTED SOLUTION
AlB
Community Champion
Community Champion

‎06-10-2022 07:26 AM

@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_ )

 

 

SU18_powerbi_badge

 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.

 

View solution in original post

Percentile.pbix
Message 12 of 13
1,348 Views
0
12 REPLIES 12
AlB
Community Champion
Community Champion

‎06-10-2022 07:26 AM

@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_ )

 

 

SU18_powerbi_badge

 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.

 

Percentile.pbix
Message 12 of 13
1,349 Views
0
MTrullàs
Helper III
Helper III
In response to AlB

‎06-10-2022 09:18 AM

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

Message 13 of 13
1,334 Views
MTrullàs
Helper III
Helper III

‎06-10-2022 05:59 AM

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"

 

Message 10 of 13
1,357 Views
0
AlB
Community Champion
Community Champion
In response to MTrullàs

‎06-10-2022 06:13 AM

@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

 

SU18_powerbi_badge

 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.

 

Message 11 of 13
1,356 Views
0
AlB
Community Champion
Community Champion

‎06-09-2022 11:03 PM

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

SU18_powerbi_badge

 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.

 

Message 2 of 13
1,395 Views
0
MTrullàs
Helper III
Helper III
In response to AlB

‎06-09-2022 11:32 PM

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:

MTrulls_1-1654842138767.png

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

 

MTrulls_0-1654842085584.png

 

Thank you again, for your help

Message 3 of 13
1,393 Views
0
AlB
Community Champion
Community Champion
In response to MTrullàs

‎06-10-2022 12:24 AM

@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_ )

 

 

SU18_powerbi_badge

 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.

 

Message 4 of 13
1,387 Views
0
MTrullàs
Helper III
Helper III
In response to AlB

‎06-10-2022 01:27 AM

Thank you so much,
Now the measure works, I mean it gives me a result, but the result is not what I'm looking for.I don't know what the NewMeasure_is specifically
I the result that I expected is that the excel shows:

 

 

MTrulls_0-1654848110255.png

 

  

Message 5 of 13
1,379 Views
0
AlB
Community Champion
Community Champion
In response to MTrullàs

‎06-10-2022 03:35 AM

@MTrullàs 

hmm... I can't see what might be going wrong

Can you share the main table? Or an extract of it, with the expected result. So that I can run it on my side and see where the problem is coming from

 

SU18_powerbi_badge

 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.

 

Message 6 of 13
1,370 Views
0
MTrullàs
Helper III
Helper III
In response to AlB

‎06-10-2022 06:57 AM

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"

 

Message 7 of 13
1,350 Views
0
MTrullàs
Helper III
Helper III
In response to MTrullàs

‎06-10-2022 07:00 AM

Sorry, now I try again,

 

FgstnrFábricaCampaSemanaMesAñoMarcaEstancia Fábrica
1MarSch14Abril2022Sea1
2MarSch14Abril2022Sea2
3MarSch14Abril2022Sea3
4MarSch14Abril2022Sea4
5MarSch14Abril2022Sea1
6MarSch14Abril2022Sea2
7MarSch14Abril2022Sea3
8MarSch14Abril2022Sea4
9MarSch14Abril2022Sea1
10MarSch14Abril2022Sea2
11MarSch14Abril2022Sea1
12MarSch14Abril2022Sea1
13MarSch14Abril2022Sea2
14MarSch14Abril2022Sea1
15MarSch14Abril2022Sea0
16MarSch14Abril2022Sea1
17MarSch14Abril2022Sea1
18MarSch14Abril2022Sea0
19MarSch14Abril2022Sea1
20MarSch14Abril2022Sea2
21MarSch14Abril2022Sea0
22MarSch14Abril2022Sea0
23MarSch14Abril2022Sea0
Message 8 of 13
1,349 Views
0
MTrullàs
Helper III
Helper III
In response to MTrullàs

‎06-10-2022 07:07 AM

More info,... thank you so much for your time.

 

FgstnrFábricaCampaSemanaMesAñoMarcaEstancia Fábrica     
1MarSch14Abril2022Sea1     
2MarSch14Abril2022Sea2  Etiquetas de filaCuenta de Estancia Fábricaobjective
3MarSch14Abril2022Sea3  0522%
4MarSch14Abril2022Sea4  1961%
5MarSch14Abril2022Sea1  2583%
6MarSch14Abril2022Sea2  3291%
7MarSch14Abril2022Sea3  42100%
8MarSch14Abril2022Sea4  Total general23 
9MarSch14Abril2022Sea1     
10MarSch14Abril2022Sea2     
11MarSch14Abril2022Sea1     
12MarSch14Abril2022Sea1     
13MarSch14Abril2022Sea2     
14MarSch14Abril2022Sea1     
15MarSch14Abril2022Sea0     
16MarSch14Abril2022Sea1     
17MarSch14Abril2022Sea1     
18MarSch14Abril2022Sea0     
19MarSch14Abril2022Sea1     
20MarSch14Abril2022Sea2     
21MarSch14Abril2022Sea0     
22MarSch14Abril2022Sea0     
23MarSch14Abril2022Sea0     
Message 9 of 13
1,349 Views
0

Helpful resources

Announcements
Fabric Data Days Carousel

Fabric Data Days

Advance your Data & AI career with 50 days of live learning, contests, hands-on challenges, study groups & certifications and more!

October Power BI Update Carousel

Power BI Monthly Update - October 2025

Check out the October 2025 Power BI update to learn about new features.

FabCon Atlanta 2026 carousel

FabCon Atlanta 2026

Join us at FabCon Atlanta, March 16-20, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.

View All