topic Re: How to Run Excel Percentile and IF Calculation in BI? in DAX Commands and Tips

How to Run Excel Percentile and IF Calculation in BI?

Anonymous — Fri, 15 May 2020 14:44:07 GMT

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

Re: How to Run Excel Percentile and IF Calculation in BI?

nandukrishnavs — Fri, 15 May 2020 15:01:13 GMT

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 🙂

Re: How to Run Excel Percentile and IF Calculation in BI?

Greg_Deckler — Fri, 15 May 2020 15:06:20 GMT

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

Re: How to Run Excel Percentile and IF Calculation in BI?

Anonymous — Sat, 16 May 2020 17:27:03 GMT

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.

Re: How to Run Excel Percentile and IF Calculation in BI?

Anonymous — Sat, 16 May 2020 17:27:35 GMT

Hi Greg,

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

Re: How to Run Excel Percentile and IF Calculation in BI?

Anonymous — Sat, 16 May 2020 18:07:56 GMT

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!!

Re: How to Run Excel Percentile and IF Calculation in BI?

Anonymous — Sat, 16 May 2020 18:39:56 GMT

Hi Harsh

Thank you very much

Works well thank you

Thanks

Johnny

Re: How to Run Excel Percentile and IF Calculation in BI?

Anonymous — Sat, 16 May 2020 18:41:26 GMT

Anonymous ,

You are Welcome.

Do Mark with @ incase you face any issues

Regards,

Harsh Nathani