Home Forums Power Pivot Nesting formulas

Tagged: ,

This topic contains 2 replies, has 2 voices, and was last updated by  sjhc1177 3 years, 2 months ago.

Viewing 3 posts - 1 through 3 (of 3 total)
  • Author
    Posts
  • #9044

    sjhc1177
    Participant
    • Started: 25
    • Replies: 28
    • Total: 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

    #9057

    tomallan
    Keymaster
    • Started: 0
    • Replies: 417
    • Total: 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

    #9058

    sjhc1177
    Participant
    • Started: 25
    • Replies: 28
    • Total: 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

Viewing 3 posts - 1 through 3 (of 3 total)

The forum ‘Power Pivot’ is closed to new topics and replies.