Measure displaying incorrect total when using discounts

[scottward626]

Hi there,

Having trouble with the following table. The total for the Revenue (After Discount) and Discount Amount aren’t adding up correctly. Tried a few things now and am aware that totals and measures don’t quite work this way.

This is the problem.

As you can see the totals for After Discount and Discount Amount don't add up. (But they do match the Revenue total)

These are the measures I’m using:

REVENUE is

m_Revenue =

    SUM(order_job[price])

REVENUE AFTER DISCOUNT

m_REVENUE (After Discount) = [m_REVENUE] - ([m_REVENUE] * SUMX(VALUES(Credit_logs[Discount %]),(Credit_logs[Discount %] /100)))

DISCOUNT AMOUNT

m_revenue (Amount discounted per credit rate) = [m_REVENUE] * SUMX(VALUES(Credit_logs[Discount %]), (Credit_logs[Discount %] /100))

This is the data the measures are referring too.

Credit_Log table

Order Table:

62797 doesn’t have a discount (use credit) so it doesn’t appear in credit_Logs, and won't have a discount applied. It does, however, appear in the Orders table as its a normal sale. This seems to be where the measure falls apart.

If I display rows that don't have a discount ONLY the total will be correct. Same if I display rows that all use the same discount amount. But as soon as it has more than 1 various discount it displays the incorrect total.

Any suggestions?

Thanks.

[v-chuncz-msft]

@scottward626 

You may try a similar way as shown here.

[scottward626]

@v-chuncz-msft Thank you for your help, much appreciated.
I'm not quite sure I understand how the suggested solution of using summarise works? I gave it a shot but I'm seeing the results I would hope for. Maybe I'm just not understanding how the solution works, can you explain?

From what I can gather, 
- summarize method is summarising a table by grouping its column. But in this case, I'm grabbing the discount amount from a calculation of two different tables. which will not work using summarise.

- if I'm wrong and summarise is actually able to give us the result, where should divide go in the new measure?

Thanks again, really appreciate the assistance

[scottward626]

Hey Team, wondering if anyone had any suggestions here? Thanks, I'm really stuck 😞