How to Run Excel Percentile and IF Calculation in BI?

[Anonymous]

Need help with translating Percetile and IF formulaes into BI. I use these in excel

Total Cost is a total of each row sales

Total Cost % is = Cell Value / Total Of Column. which is then % of total cost. 

The ABC column runs an IF and Percentile to select top 80% = A, 10-20% = B, 2-10% = C, less than 1% = D

2. Precentile formuale works on =IF(S2>=PERCENTILE(S:S,0.8)=TRUE,"A","0") , 

It works in excel but I am stuck getting this to work in BI. 

I cannot get a text return on the IF, am only getting a numeric return.

Would really appreciate any help

[Greg_Deckler]

Perhaps this will help: https://community.powerbi.com/t5/Community-Blog/Excel-to-DAX-Translation/ba-p/1060991

[Anonymous]

Hi Greg,

Thank you, that has been a great guide for me. 

[nandukrishnavs]

@Anonymous 

Total Cost % = Table1[Total Cost]/SUM(Table1[Total Cost])

Then apply if condition to categorize into A, B, C

Did I answer your question? Mark my post as a solution!
Appreciate with a kudos 🙂

[Anonymous]

Thanks for your time to respond on this.

When running the percentile and IF calculation it will not output a text

For example If percentile <0.8,"A", this is where it will not accept the A, but it will accept numbers. 

[harshnathani · Accepted solution]

Hi @Anonymous ,

Please use the below measure. Have used some dummy value.

Table Name = Table6

Sum of Values = SUM(Table6[Values])

%Sum of Values = DIVIDE([Sum of Values], CALCULATE([Sum of Values],ALL(Table6)))

Rate =
VAR _2P =
    PERCENTILEX.INC (
        ALL ( table6 ),
        Table6[%Sum of Values],
        .02
    )
VAR _10P =
    PERCENTILEX.INC (
        ALL ( table6 ),
        Table6[%Sum of Values],
        .10
    )
VAR _20P =
    PERCENTILEX.INC (
        ALL ( table6 ),
        Table6[%Sum of Values],
        .20
    )
VAR _80P =
    PERCENTILEX.INC (
        ALL ( table6 ),
        Table6[%Sum of Values],
        .80
    )
RETURN
    SWITCH (
        TRUE (),
        Table6[%Sum of Values] > _80P, "A",
        Table6[%Sum of Values] > _20P
            && Table6[%Sum of Values] < _10P, "B",
        Table6[%Sum of Values] >= _10P
            && Table6[%Sum of Values] < _2P, "C",
        Table6[%Sum of Values] <= _2P, "D",
        "Not Defined"
    )

Regards,

Harsh Nathani

Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!!

[Anonymous]

Hi Harsh

Thank you very much

Works well thank you

Thanks 

Johnny

[harshnathani]

@Anonymous ,

You are Welcome.

Do Mark with @ incase you face any issues

Regards,

Harsh Nathani