Table Column with same value for each row on measure

[jordanxie]

Hi,

I have a measure for 95th Percentile based on SOL Created On date as below. Each row will have a different Demonstrated Lead Time but would like the 95th Percentile column to be the same for each row. In the example, it would be 182 for each row. 

Any help is appreciated.

95th Percentile = PERCENTILEX.INC(

SUMMARIZE(Demonstrated_Lead_Time_1, Demonstrated_Lead_Time_1[SOL Created On] ),

CALCULATE(AVERAGE(Demonstrated_Lead_Time_1[Demonstrated Lead Time])),

0.95

)

[v-shex-msft]

Hi @jordanxie,

Can you please share some dummy data(with raw table schema) and expected results? It should help us clarify your scenario and test to coding formula.

How to Get Your Question Answered Quickly  

Regards,

Xiaoxin Sheng

[jordanxie]

Hi @v-shex-msft 

Let's say the 95th Percentile for the sample data is 8 based on the formula. The value changes based on the dynamic date selection for the SOL Created On date.

The expected result is that value for 95th Percentile will be the same (in this case = 😎 on all rows of the table in the last column.

95th Percentile = PERCENTILEX.INC(

SUMMARIZE(Demonstrated_Lead_Time_1, Demonstrated_Lead_Time_1[SOL Created On] ),

CALCULATE(AVERAGE(Demonstrated_Lead_Time_1[Demonstrated Lead Time])),

0.95

)

Material	Sales Document	Sales Doc Item	TRLT	Actual GI Date	Requested Delivery Date	Demonstrated Lead Time	95th Percentile
12511	100452	000010	3	2020-11-16	2020-11-13	3	8
12511	100455	000180	3	2020-12-02	2020-11-27	5	8
12511	100455	000170	3	2020-12-02	2020-11-27	5	8
12511	100474	000080	3	2021-02-19	2021-02-11	8	8

[v-shex-msft]

HI @jordanxie,

What the 'SOL Created on' field means? It seems not include in the sample table that you shared.

Regards,

Xiaoxin Sheng

[jordanxie]

'SOL Created on' is the Sales Order Line Created On Date that is used in the formula.

[Greg_Deckler]

@jordanxie  I believe:

95th Percentile = PERCENTILEX.INC(
SUMMARIZE(ALL(Demonstrated_Lead_Time_1), Demonstrated_Lead_Time_1[SOL Created On] ),
CALCULATE(AVERAGE(Demonstrated_Lead_Time_1[Demonstrated Lead Time])),
0.95
)

Hard to say for certainty without sample data, etc. But you are going to want an ALL in there somewhere to break out of the context of the visual and consider all of the data.

[jordanxie]

Hi @Greg_Deckler 

Tried the suggested formula but it doesn't work. The 'Card' used actually produced 'Blank' as the 95th Percentile.