Calculating Percentage of total (not using new QuicCalc Feature) - getting a NaN or 0 for percentage

[heathernicole]

I am trying to calculate the percentage of total manually in regards to Credit Vs. Total Sales.

For Example:

Credits for June: ($100)

Invoiced for June: $10,000

Unless I'm missing something the basic calculation for this would be

(Credits  / Invoiced) * 100

Here is my formula in Power BI:

Credit Percentage = CALCULATE(DIVIDE(SUM('SALES DETAILS'[Credit Amount]), SUM('SALES DETAILS'[Invoice Amount])) * 100)

The data is from Quickbooks (which is always seems to be a pain to work with due to the database structure).

For invoice amount, my data looks like this: 

Credit Raw data looks like this:

I've tried every combination of this type. I either get 0.00 or Nan. Every now and then I'll get a funky number.

This seems simple enough... I can't figure out why it won't just work. Any ideas or tips would be extremely helpful. 🙂 

Thanks in advance!

~H

[heathernicole]

@Vvelarde 

I tried the code that you sent - Credit% = DIVIDE((SUM('Sales Details'[Credit Amount])),(SUM('Sales Details'[Invoice Amount])))*100

And I got an incorrect value -  (-65.86). I have no idea how it's coming up with that number. 😕

Here are the figures I'm testing with:

It should calculate to be - 1.55% as the end result

[heathernicole · Accepted solution]

Figured it out!!! 😄 

Instead of Credit% being a column it needs to be a MEASURE. (this deserves a little bit of a palm slap). 

I had tried a measure before but I may not have had the calculation right. See below. 🙂 

@Vvelarde

[Sean]

@heathernicole I'm surprised you didn't add an appropriate picture to accompany the post above (like you always do )

[heathernicole]

@Sean   I ended up taking out the multiply by 100 so I could get just the initial decimal figure then changed the data type to percentage. That worked a lot better. 🙂 

[heathernicole]

@Vvelarde - Hello! 🙂 I'm getting emails that show your responses - but I can't see them when I come to this post - not sure why?

[ianbarker]

Something like this should give you what you need:

Credit Percentage = CALCULATE(SUM(Table[Credit Amount])/SUM(Table[Invoice Amount]))

Stick *-1 on the end if you want the percentage to appear positive.

[Vvelarde]

hi @heathernicole

Credit% = Divide(Sum('Sales Details[Credit Amount]);(SUM('Sales Details[Invoice Amount])))*100