Table is not adding individual lines correctly

[Anonymous]

Hello, 

I would like my measures to accurately sum in the total line when using a table (see screenshot). Estimated Coupon Redemption $ Measure and Program cost measure aren't adding correctly even though the calcualtion in the individual lines is correct. 

Estimated Coupon Redemption $ Measure = [Estimated Redemption Rate Measure]* [Face Value Measure] *[Distribution Qty Total]

Insertion Cost Measure = CALCULATE([Insertion Cost $], FILTER('Inmar Full Monty','Inmar Full Monty'[Redemption Date]=MIN('Inmar Full Monty'[Redemption Date])))

Any suggestions? I tried the summarize formula but I must not be writing them correctly because they sum the totals but then the calculations in the individual lines are incorrect. 

I appreciate the help!

[v-jiascu-msft]

Hi @Anonymous,

Did it work? Could you please mark the proper answers as solutions?

Best Regards,

Dale

[Anonymous]

No it didn't work, it is giving me an error since it doesn't recognize the [PerRow] at the end of the formula. 

I also wanted to get the same result of the total row adding up with a formula in my measure see below:

Distributed coupons = MINX('Master Offer Code', 'Master Offer Code'[DistributedQuantity])

But it says that the total is zero. 

[v-jiascu-msft]

Hi @Anonymous,

Can you share your file or a dummy sample? The result of DAX formula largely depends on the context and the data structure.

Best Regards,

Dale

[Anonymous]

please see example below of the two collums that will not add up. The measures for those colums are:

Coupons Distributed Ct = MINX('Master Offer Code', 'Master Offer Code'[DistributedQuantity])

Insertion Cost Dollars = SUMX(VALUES('Master Offer Code'[OfferCode]), MIN('Master Offer Code'[Insertion Cost]))

it think it has something to do with me using MINX but i need to do that because the way the source data is set up the coupons distributed are repeated each month which is inaccurate since we only distribute it once.

[v-jiascu-msft]

Hi @Anonymous,

Since there are measures, I would suggest you try the formula below.

Estimated Coupon Redemption $ Measure =
SUMX (
    SUMMARIZE (
        'Inmar Full Monty',
        'Inmar Full Monty'[OfferCode],
        'Inmar Full Monty'[Method],
        "PerRow", 'Inmar Full Monty'[Estimated Redemption Rate] * 'Inmar Full Monty'[FaceValue]
            * 'Inmar Full Monty'[Distribution Quantity]
    ),
    [PerRow]
)

Best Regards,

Dale

[Anonymous]

Hey @Anonymous

This is a common ask and the solution isn't too hard when you see it written out. I created a video tutorial on this method here:

https://www.youtube.com/watch?v=4NgQLqHI6hU

Hope this helps,

Parker

[Anonymous]

I followed the same forma tyour formula in the video but it returned a blank value, see below:

Estimated Coupon Redemption $ Measure = IF(ISFILTERED('Inmar Full Monty'[OfferCode]), SUM('Inmar Full Monty'[Estimated Redemption Rate]) * SUM('Inmar Full Monty'[Coupon Face Value]) * SUM('Inmar Full Monty'[Distribution Quantity]), SUMX('Inmar Full Monty', 'Inmar Full Monty'[Estimated Redemption Rate]*'Inmar Full Monty'[FaceValue]*'Inmar Full Monty'[Distribution Quantity]))

[Anonymous]

Try adding ALLSELECTED to your table reference in the SUMX. Not sure if that will solve the problem but it looks like you might need it