10 Replies Latest reply on Apr 20, 2018 2:58 PM by Jeff West

# Multi dimension calculation.  Level of Detail?  Set?  Other?

I am attempting to create calculations based on 3 different dimensions and 1 measure.  Project, Part Number, Mfg PN, Price.    For each project I need to know the % of Part Numbers that are unpriced, where there are potentially multiple MFG PNs for each PN that may have a price.  I want to know which PNs do not have price for any/all of it's MFG PNs (include zero and null).

Calculations as it would be in excel are in attached example.  I can't figure out the best (or any) way to do this.

Jeff West

Legal Disclaimer :

The information contained in this message may be privileged and confidential.

It is intended to be read only by the individual or entity to whom it is addressed

or by their designee. If the reader of this message is not the intended recipient,

you are on notice that any distribution of this message, in any form,

is strictly prohibited. If you have received this message in error,

please immediately notify the sender and delete or destroy any copy of this message!

• ###### 1. Re: Multi dimension calculation.  Level of Detail?  Set?  Other?

Why your message has legal Disclaimer?

• ###### 2. Re: Multi dimension calculation.  Level of Detail?  Set?  Other?

it returned this

first I filled the nulls with 0

then ID those without price

then counted and divided

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.

• ###### 3. Re: Multi dimension calculation.  Level of Detail?  Set?  Other?

Definitely helping me along.  In my example sheet Project 1 had 3 unpriced of 14 total unique; This produced 5 of 14.  Project 2 matched exactly.  I am still going through this to try to figure out the difference.

• ###### 4. Re: Multi dimension calculation.  Level of Detail?  Set?  Other?

I'm not getting it.  I replaced the formula in lod part with no price with

{ INCLUDE [Part Number],[Mfg Partno],[Project Name]:sum(

if [Price] <= 0 then 1 else 0 end)}

and was able the correct value of 3, but when I apply this to my larger data set it is not working....  I've also tried ZN (Sum( If price .... )}

I've also tried taking out MFG Partno from the INCLUDE portion.

• ###### 5. Re: Multi dimension calculation.  Level of Detail?  Set?  Other?

did you use the fill null calculation    ifnull([Price],0)  to fill the null prices with 0 and then use that expression in the LOD

Jim

• ###### 6. Re: Multi dimension calculation.  Level of Detail?  Set?  Other?

Yes, both ways.   Can you get your original example to calculate the value of 3 for Count of Parts with No Price for Project 1?

• ###### 7. Re: Multi dimension calculation.  Level of Detail?  Set?  Other?

I still get 5 -

3 with \$0 for price and 2 with null values - you did want to include those did not?

• ###### 8. Re: Multi dimension calculation.  Level of Detail?  Set?  Other?

Here is the data extract.  CAPPING_001 and CAPPING_017

Should not be included because the sum of their prices is not zero.

Thanks.

Jeff

• ###### 9. Re: Multi dimension calculation.  Level of Detail?  Set?  Other?

see the attached

this is the change I made

it returns this

• ###### 10. Re: Multi dimension calculation.  Level of Detail?  Set?  Other?

Bingo!  I can relax this weekend...  Thanks!!