Strange % result

[dusdau]

Hi All,

I am trying to determine why I am getting a certain result with my measures.  I cannot make sense of it and am certain I am missing something very basic but for the life of me I can't figure out what it is!

Here is the table with results:

I am trying to determine how it is coming up with that huge percentage for Gross Profit %.

Model/Columns/Measures in the table are setup as follows:

Sales[USD Sales Fixed Curr] (column)

Sales[USD Cost Fixed Curr] (column)

USD GP Fixed Curr = Sales[USD Sales Fixed Curr] - Sales[USD Cost Fixed Curr]  (calculated column)

Total Sales (USD Fixed) = SUM ( Sales[USD Sales Fixed Curr] ) (measure)

Total Cost (USD Fixed) = SUM ( Sales[USD Cost Fixed Curr] ) (measure)

Total Gross Profit (USD Fixed) = [Total Sales (USD Fixed)] - [Total Cost (USD Fixed)] (measure)

Gross Profit % = DIVIDE ( [Total Gross Profit (USD Fixed)], [Total Sales (USD Fixed)] ) (measure)

I've looked for small decimals in the amounts but as far as I can tell the Gross Profit % here should be 0.

What am I missing here?

[Anonymous]

Hi @dusdau 

You can create a calculated column to count the percentage instead of measure .

Column = [Total Gross Profit (USD Fixed)]/[Total Sales (USD Fixed)]

The result is as shown :

Best Regard

Community Support Team _ Ailsa Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[dusdau]

Hi Ailsa,

Thanks, but a sum of the individual %'s is not what I am looking for here.  I'm looking to understand why 

DIVIDE ( [Total Gross Profit (USD Fixed)], [Total Sales (USD Fixed)], 0) 

Is evaluating to a huge percentage on the aggregate/total line....when it seems like 1.24 divided by 0 should be 0.

[Ashish_Mathur · Accepted solution]

Hi,

The denominator is probably not exactly a 0.  It could be a very small decimal number.  Use the ROUND function in the numerator and denominator to keep only 2 decimal places.

[dusdau]

Wow, it was so small i could not see the decimal place even when expanding the decimals out.  Putting a ROUND on it confirmed this...and I feel like an idiot now. 🙂  Thanks for the help.

[Ashish_Mathur]

You are welcome.

[ALLUREAN]

You are dividing by 0 and this functions should work well, try this:

Gross Profit % = DIVIDE ( [Total Gross Profit (USD Fixed)], [Total Sales (USD Fixed)] ,0)

[dusdau]

Thanks Allurean, I've tried that previously and same result unfortunately.  I think the DIVIDE function defaults to zero if you don't specify that 3rd parameter.