topic Re: Percentile in DAX Commands and Tips

Percentile

MTrullàs — Fri, 10 Jun 2022 04:55:39 GMT

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

)

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,

Re: Percentile

AlB — Fri, 10 Jun 2022 06:03:00 GMT

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.

Re: Percentile

MTrullàs — Fri, 10 Jun 2022 06:32:09 GMT

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

Re: Percentile

AlB — Fri, 10 Jun 2022 07:41:02 GMT

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.

Re: Percentile

MTrullàs — Fri, 10 Jun 2022 08:27:28 GMT

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:

Re: Percentile

AlB — Fri, 10 Jun 2022 10:35:32 GMT

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

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.

Re: Percentile

MTrullàs — Fri, 10 Jun 2022 12:59:13 GMT

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"

Re: Percentile

AlB — Fri, 10 Jun 2022 13:13:53 GMT

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.

Re: Percentile

MTrullàs — Fri, 10 Jun 2022 13:57:22 GMT

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"

Re: Percentile

MTrullàs — Fri, 10 Jun 2022 14:00:19 GMT

Sorry, now I try again,

Fgstnr	Fábrica	Campa	Semana	Mes	Año	Marca	Estancia Fábrica
1	Mar	Sch	14	Abril	2022	Sea	1
2	Mar	Sch	14	Abril	2022	Sea	2
3	Mar	Sch	14	Abril	2022	Sea	3
4	Mar	Sch	14	Abril	2022	Sea	4
5	Mar	Sch	14	Abril	2022	Sea	1
6	Mar	Sch	14	Abril	2022	Sea	2
7	Mar	Sch	14	Abril	2022	Sea	3
8	Mar	Sch	14	Abril	2022	Sea	4
9	Mar	Sch	14	Abril	2022	Sea	1
10	Mar	Sch	14	Abril	2022	Sea	2
11	Mar	Sch	14	Abril	2022	Sea	1
12	Mar	Sch	14	Abril	2022	Sea	1
13	Mar	Sch	14	Abril	2022	Sea	2
14	Mar	Sch	14	Abril	2022	Sea	1
15	Mar	Sch	14	Abril	2022	Sea	0
16	Mar	Sch	14	Abril	2022	Sea	1
17	Mar	Sch	14	Abril	2022	Sea	1
18	Mar	Sch	14	Abril	2022	Sea	0
19	Mar	Sch	14	Abril	2022	Sea	1
20	Mar	Sch	14	Abril	2022	Sea	2
21	Mar	Sch	14	Abril	2022	Sea	0
22	Mar	Sch	14	Abril	2022	Sea	0
23	Mar	Sch	14	Abril	2022	Sea	0

Re: Percentile

MTrullàs — Fri, 10 Jun 2022 14:07:28 GMT

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

Fgstnr	Fábrica	Campa	Semana	Mes	Año	Marca	Estancia Fábrica
1	Mar	Sch	14	Abril	2022	Sea	1
2	Mar	Sch	14	Abril	2022	Sea	2	Etiquetas de fila	Cuenta de Estancia Fábrica	objective
3	Mar	Sch	14	Abril	2022	Sea	3	0	5	22%
4	Mar	Sch	14	Abril	2022	Sea	4	1	9	61%
5	Mar	Sch	14	Abril	2022	Sea	1	2	5	83%
6	Mar	Sch	14	Abril	2022	Sea	2	3	2	91%
7	Mar	Sch	14	Abril	2022	Sea	3	4	2	100%
8	Mar	Sch	14	Abril	2022	Sea	4	Total general	23
9	Mar	Sch	14	Abril	2022	Sea	1
10	Mar	Sch	14	Abril	2022	Sea	2
11	Mar	Sch	14	Abril	2022	Sea	1
12	Mar	Sch	14	Abril	2022	Sea	1
13	Mar	Sch	14	Abril	2022	Sea	2
14	Mar	Sch	14	Abril	2022	Sea	1
15	Mar	Sch	14	Abril	2022	Sea	0
16	Mar	Sch	14	Abril	2022	Sea	1
17	Mar	Sch	14	Abril	2022	Sea	1
18	Mar	Sch	14	Abril	2022	Sea	0
19	Mar	Sch	14	Abril	2022	Sea	1
20	Mar	Sch	14	Abril	2022	Sea	2
21	Mar	Sch	14	Abril	2022	Sea	0
22	Mar	Sch	14	Abril	2022	Sea	0
23	Mar	Sch	14	Abril	2022	Sea	0

Re: Percentile

AlB — Fri, 10 Jun 2022 16:28:46 GMT

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.

Re: Percentile

MTrullàs — Fri, 10 Jun 2022 16:18:49 GMT

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