Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Applying a Percentile on a Graph

Hi all,    I am trying to work with a dataset that has these columns -  Reporting Date Form Type ID Actual Days 12/2/2021 Submittal 01E-XXX-00002 467 12/2/2021 Field Observation ...
Need Help
  • v-luwang-msft's avatar
    v-luwang-msft
    4 years ago

    Hi felixthecatx ,

    According to your description, 1045 data, 95 percent, 1045 * 0.95 = 992.75, that is, take the data after 993, if according to my previous method, when more than one duplicate value, may be all selected.

    like below:

    In my opinion, if you want to reach duplication with only one random peer, you need to create auxiliary index rows to ensure that 5 percent is 1.

    insert index:

    Then adjust measure to the below:

    test1 = 
VAR RANK1 =
    RANKX ( ALL ( 'Table' ), CALCULATE ( SUM ( 'Table'[value] )+0.0000001*SUM('Table'[Index]) ) )
VAR MIN1 =
    CALCULATE ( COUNTROWS ( 'Table' ), ALL ( 'Table' ) ) * 0.05
RETURN
    IF ( RANK1 <= MIN1, 1, 0 )

    final:

     

    Best Regards

    Lucien

Anonymous's avatar
Anonymous
Not applicable
4 years ago

To expand on this issue, I have also formulated this measure = 

95% Data = 
VAR _sum =
    SUM (Data[Actual Days])
VAR _top = 
    CALCULATE(
        COUNTROWS( ADDCOLUMNS( VALUES(Data[Form ID]), "_sum", _sum)),
        REMOVEFILTERS(Data[Form ID])
    ) * 0.95
RETURN
    CALCULATE(
        CALCULATE( AVERAGEA (Data[Actual Days])),
        KEEPFILTERS( 
            TOPN(
                _top,
                ALLSELECTED( Data[Form ID]),
                CALCULATE( AVERAGEA (Data[Actual Days]) ), ASC
            )
        )
    )

This seems to produce an average of the Actual Day values per Form ID with 5% excluded, which is great however - whenever I try to use this measure with other columns (namely the Reporting Date column) it breaks and the data no longer makes sense.