Nesting formulas

[sjhc1177Participant Started: 25Replies: 28Total: 53]

Hi I have 3 measures. They all work fine on their own. But now I would like to have it evaluate when all 3 conditions are met.

I cant seem to get it to work.

MUGS UNITS SOLD:=CALCULATE([SALES TOTAL UNITS SOLD],(‘tblInvSkuMaster'[<wbr />brand_code]=”CANA10″))

MUGS TRANS# DISTINCT COUNT:=CALCULATE(DISTINCTCOUNT<wbr />(‘SALES TABLE'[TRANS#]),’SALES TABLE'[UNITS SOLD]>0,tblInvSkuMaster[brand_<wbr />code]=”CANA10″)-CALCULATE(DIST<wbr />INCTCOUNT(‘SALES TABLE'[TRANS#]),’SALES TABLE'[UNITS SOLD]<0,tblInvSkuMaster[brand_<wbr />code]=”CANA10″)

MUGS RETAIL:=CALCULATE([SALES TOTAL NET RETAIL])

I would like the formula to evaluate if MUGS UNITS SOLD”1, MUGS TRANS# DISTINCT COUNT=1 AND MUGS RETAIL

[tomallanKeymaster Started: 0Replies: 417Total: 417]

A few questions for you that will help in finding an answer for you:

Is [SALES TOTAL NET RETAIL] a measure or a column? If a measure, then the outer CALCULATE of CALCULATE([SALES TOTAL NET RETAIL]) is unnecessary because the formula engine wraps all measures within a CALCULATE. The extra CALCULATE could be giving you a problem if you are combining this formula inside of another CALCULATE

In your formula for [MUGS Trans# Distinct Count], it looks like you are subtracting the count of transactions for units returned from the count of transactions for units sold. Is that correct?

Also, what are the formulas for [Sales Total Units Sold] and [Sales Total Net Retail]? Am curious if you are using a SUM or SUMX.

When you say you the combined formulas do not work, do you get an error message (if so, what does it say?) or do you get a value and it is not what you expect?

The final sentence shows MUGS UNITS SOLD”1 did you mean to write MUGS UNITS SOLD = 1?

At the very end, [Mugs Retail] is listed without a matching value, did you mean that [Mugs Retail] should also be equal to 1 or are you checking if [Mugs Retail] is any non-zero value or something else?

Please insert entire formula that you are trying to evaluate.

Tom

[sjhc1177Participant Started: 25Replies: 28Total: 53]

Hi, thanks for reading my post.

• SALES TOTAL NET RETAIL is a measure. SALES TOTAL NET RETAIL:=sum(‘SALES TABLE'[NET RETAIL])
• SALES TOTAL UNITS SOLD:=sum(‘SALES TABLE'[UNITS SOLD])
• TRANS# DISTINCT COUNT:=CALCULATE(DISTINCTCOUNT(‘SALES TABLE'[TRANS#]),’SALES TABLE'[UNITS SOLD]>0)-CALCULATE(DISTINCTCOUNT(‘SALES TABLE'[TRANS#]),’SALES TABLE'[UNITS SOLD]<0)
• In your formula for [MUGS Trans# Distinct Count], I’m trying to make sure if in the power pivot table the TRANS# is listed more than once. It is counted only once.

What i want to achieve is that if only 1 MUG is sold in a single transaction, to extract the value of the sale. We pay commission to our staff. But when they sell only 1 MUG in a transaction they get 1/2 the normal commission. The commission is in another table. So having it indexed is for a later question. LOL.

I really appreciate all the help people like you provide. It has helped me a lot in my carer and I also try to give back.

 

Cheers

[Author]

Posts