5 Replies Latest reply on Sep 12, 2019 6:57 AM by Jim Dehner

# Division & LOD Calculation Problem

Hello,

I am struggling with some division operation and LOD calculation problems here (workbook attached). What I am trying to achieve are

1. calculate an average margin (sales per unit quantity) for each given product in all 3 regions (R1, R2, & R3; excluding zeros and nulls), and
2. calculate a value for each given product by multiplying the above margin average with the quantities in 2 regions only.

My first problem was that when I tried to calculate the average, the division operation did not seem to work (supposedly due to an aggregation versus non-aggregation issue?)

My calculated field for the average margin ([Average margin/kg]) is as follows:

My second struggle was that when I tried to sum up the quantities in 2 select regions, the values did not seem to add up correctly,

and that the simple multiplication did not look right, either:

Your comments or suggestions are much appreciated. Thank you in advance for your time and help.

Best regards,

Christine

• ###### 1. Re: Division & LOD Calculation Problem

I opened your workbook and started going through the hierarchy of calculations

you are consistently neglecting to aggregate the measures in your formulas

for example if you write   A/B   what happens is for every record  A is divided by B and when you bring that to the viz it is aggregated with sum  - that will total all rhe individulal results at the revord level - (add up all the a/b's together)

the correct way is to write   sum(A)/Sum(B)  this will add up all the A's and all the B's then divide the total A by the total B

you will need to apply the same logic throughout you book to get the correct results

and remember LOD's are not aggregates and need to aggregate as above

Jim

If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

1 of 1 people found this helpful
• ###### 2. Re: Division & LOD Calculation Problem

Hi, Christine

Not very sure what's your expected result, but please find my attemp attached.

Below is the result

Hope this helps

ZZ

• ###### 3. Re: Division & LOD Calculation Problem

Hello ZZ,

Thank you! That is exactly the result I wish to get.

And, wow, so an IF function can go within the SUM aggregation. A new lesson learned. Really appreciate it!

Best regards,

Christine

• ###### 4. Re: Division & LOD Calculation Problem

Hello Jim,

Thank you for the clarification! Indeed, I constantly struggle with when and what type of aggregation should be applied. Reading more on this subject does help. Thanks again for your reply and for making it a bit more comprehensible.

Best regards,

Christine

• ###### 5. Re: Division & LOD Calculation Problem

glad to be of help - it  it really helped you lease mark the response helpful to acknowledge the effort'Jim