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: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

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
749 Views
0
1 ACCEPTED SOLUTION
AlB
Super User
Super User

‎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
660 Views
0
12 REPLIES 12
AlB
Super User
Super User

‎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
661 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
646 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
669 Views
0
AlB
Super User
Super User
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
668 Views
0
AlB
Super User
Super User

‎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
707 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
705 Views
0
AlB
Super User
Super User
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
699 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
691 Views
0
AlB
Super User
Super User
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
682 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
662 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
661 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
661 Views
0

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

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

View All
Top Solution Authors
View All