Home › Forums › Power Pivot › Nesting formulas
Tagged: dax, PowerPivot
This topic contains 2 replies, has 2 voices, and was last updated by sjhc1177 6 years, 1 month ago.
-
AuthorPosts
-
January 31, 2018 at 10:31 pm #9044
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
February 1, 2018 at 3:49 pm #9057A 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
February 1, 2018 at 4:10 pm #9058Hi, 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
-
AuthorPosts
The forum ‘Power Pivot’ is closed to new topics and replies.