cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
EP89 Frequent Visitor
Frequent Visitor

Table is not adding individual lines correctly

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])))

 

Coupons PBI.JPG

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!

8 REPLIES 8
Anonymous
Not applicable

Re: Table is not adding individual lines correctly

Hey @EP89

 

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

EP89 Frequent Visitor
Frequent Visitor

Re: Table is not adding individual lines correctly

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
Not applicable

Re: Table is not adding individual lines correctly

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

Community Support Team
Community Support Team

Re: Table is not adding individual lines correctly

Hi @EP89,

 

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

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Community Support Team
Community Support Team

Re: Table is not adding individual lines correctly

Hi @EP89,

 

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

 

 

Best Regards,

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
EP89 Frequent Visitor
Frequent Visitor

Re: Table is not adding individual lines correctly

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. 

Community Support Team
Community Support Team

Re: Table is not adding individual lines correctly

Hi @EP89,

 

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

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
EP89 Frequent Visitor
Frequent Visitor

Re: Table is not adding individual lines correctly

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.

 

Capture.JPG