cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
jbrijalba Frequent Visitor
Frequent Visitor

Creating a percentage based on the first digit in a number

I am creating a Benford's analysis table based on Journal Entries. I have taken the absolute value for each Amount in the ledger and pulled the first digit from it (named the column First Digit). That part works fine, my calculations start to bog down when I do my percentage calculation. Percentage = Count(First digit)/ CountRow(Table). I put into a bar graph as the value and First Digit as the axis. It looks like a Benford's table but the numbers are off. For instance, the 1 digit has a value of about 1043 the 2 digit is around 800 and so on and so forth. I pulled the count of First Digit onto a table visual and manually divided the row total by the grand total and it is giving the expected correct result. 

 

An ideas why the percentage calculation in Power BI is not working aas expected?

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Creating a percentage based on the first digit in a number

Hi @jbrijalba 

It works on my side.

Create measures:

count = COUNT(Sheet8[number])

count_all = CALCULATE(COUNTROWS(Sheet8),ALL(Sheet8[number]))

per = [count]/[count_all]

6.png

Best Regards
Maggie

 

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

2 REPLIES 2
Community Support Team
Community Support Team

Re: Creating a percentage based on the first digit in a number

Hi @jbrijalba 

It works on my side.

Create measures:

count = COUNT(Sheet8[number])

count_all = CALCULATE(COUNTROWS(Sheet8),ALL(Sheet8[number]))

per = [count]/[count_all]

6.png

Best Regards
Maggie

 

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

jbrijalba Frequent Visitor
Frequent Visitor

Re: Creating a percentage based on the first digit in a number

I forgot to use the calculate(all) function. Worked for me, I just had to use:

Calulate(Countrows(Table), Filter(All(Table), FirstDigit <>0)).

 

Thank you @v-juanli-msft