Forum Discussion

jordanxie's avatar
jordanxie
Icon for Helper I rankHelper I
5 years ago

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
)
 

 

Need Help

6 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    5 years ago

    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's avatar
      jordanxie
      Icon for Helper I rankHelper I
      5 years ago

      Hi Greg_Deckler 

       

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

    • jordanxie's avatar
      jordanxie
      Icon for Helper I rankHelper I
      5 years ago

      Hi Anonymous 

      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
      • Anonymous's avatar
        Anonymous
        Not applicable
        5 years ago

        HI jordanxie,

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

        Regards,

        Xiaoxin Sheng