Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
jordanxie
Helper I
Helper I

Table Column with same value for each row on measure

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
)
 
jordanxie_0-1620167626546.png

 

6 REPLIES 6
v-shex-msft
Community Support
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

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
)

MaterialSales DocumentSales Doc ItemTRLTActual GI DateRequested Delivery DateDemonstrated Lead Time95th Percentile
1251110045200001032020-11-162020-11-1338
1251110045500018032020-12-022020-11-2758
1251110045500017032020-12-022020-11-2758
1251110047400008032021-02-192021-02-1188

HI @jordanxie,

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

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

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

Greg_Deckler
Super User
Super User

@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.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Hi @Greg_Deckler 

 

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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