Incorrect calculated measure total

[Kimbornhoft]

Hi,

I have a new measure 

Total Shipped Value = (sum(REV_HistoryReport[Total Li Valu Amt]) /sum(REV_HistoryReport[Ord Bill Unit Qty]) * sum(OpenOrders[Qty Filled]))

which works correctly except when the OpenOrders[Qty Filled] field is a 0. Zero is returned but the cumulative total is not correct.  How do I add in an If/Then statement which says complete the calculation except when a 0 is present, then just return a 0? Or do I change the 0 in the OpenOrders[Qty Filled] field to a dash so that it isn't recognized as a number and just skips it.

Thanks for the help. 

[GilbertQ]

Hi @Kimbornhoft

Ideally you can use the new DIVIDE DAX function, which will allow you to set a figure when there is a divide by zero error.

Total Shipped Value =
DIVIDE (
    SUM ( REV_HistoryReport[Total Li Valu Amt] ),
    SUM ( REV_HistoryReport[Ord Bill Unit Qty] ) * SUM ( OpenOrders[Qty Filled] ),
    0
)

This will return a zero if there are no values.

[Kimbornhoft]

Thank you for replying, unfortunately, this formula returned all lines as $0.

[Anonymous]

@GilbertQ is correct in that using DIVIDE( ) is a good idea to avoid divide by zero errors.   But in your case, you are just doing something "mathematically wrong".

Quantity    Price   SubTotal

2                3         6

3                4         12

Here the total is 18 (6+12), it is not  sum(quant) * sum(price) = 5 * 12 = 60.

I think you have something similiar going on?  Think we will need to see some sample data.

You might try:

Total Shipped Value = SUMX(REV_HistoryReport, CALCULATE(sum(REV_HistoryReport[Total Li Valu Amt])/sum(REV_HistoryReport[Ord Bill Unit Qty]) * sum(OpenOrders[Qty Filled])))

[Kimbornhoft]

This solves the issue 99% of the time. Here is an exception:  the correct total is $59,051 but Power BI is doubling that total when an item appears muliple times to $118,102.

3131BE	1/10/2017	$29,526	189
3131BE	1/10/2017	$29,526	189

 

[Anonymous]

I'm not totally sure which table we are talking about, but... errr, uhh... if we are talking about double rows in REV_HistoryReport,  maybe inside the CALCULATE  divide by COUNTROWS(REV_HistoryReport) ?

[Kimbornhoft]

I have used this string,

Total Shipped Value = SUMX(REV_HistoryReport, CALCULATE(sum(REV_HistoryReport[Total Li Valu Amt])/sum(REV_HistoryReport[Ord Bill Unit Qty]) * sum(OpenOrders[Qty Filled])))

but I'm not sure where to incorporate the count rows  Could you help with this?

[Kimbornhoft]

You were right in assuming it is the REV history report.

[Anonymous]

Total Shipped Value =
SUMX (
    REV_HistoryReport,
    CALCULATE (
        SUM ( REV_HistoryReport[Total Li Valu Amt] )
            / SUM ( REV_HistoryReport[Ord Bill Unit Qty] )
            * SUM ( OpenOrders[Qty Filled] )
            / COUNTROWS(REV_HistoryReport)
    )
)

[Kimbornhoft]

Unfortunately, formula still isn't returning the correct values. I'm not sure where to go next.

[Kimbornhoft]

Hi

I'm going back to the beginning. Unfortunately, the suggestions above have not worked. All of the values in this table are correct except for the new measure field. It should be $2,260,502. The new measure is

Total Shipped Value = (sum(REV_HistoryReport[Total Li Valu Amt]) /sum(REV_HistoryReport[Ord Bill Unit Qty]) * sum(OpenOrders[Qty Filled]))

It seems to be related to the math that happens when a qty filled column is 0.

[Anonymous]

Maybe already answered above, but double checking here... your Total Shipped Value... is that a measure or a calculated column?

[Kimbornhoft]

It is a measure.

[Anonymous]

Ya, that won't math correctly.  If math was a verb.

1  2

2  3

3  4

(1+2+3) * (2 + 3 + 4) = 6 * 9 = 54.

(1*2) + (2*3) + (3*4) = 2 + 6 + 12 = 20.

You want the latter, but are doing the fomer.

I would think...

Total Shipped Value = sumx(REV_HistoryReport, REV_HistoryReport[Total Li Valu Amt]) / REV_HistoryReport[Ord Bill Unit Qty] * OpenOrders[Qty Filled])

But... I'm skeptical that is going to handle OpenOrders correctly.  How are those tables related?

[Kimbornhoft]

I've tried this measure but it includes this error message

[Anonymous]

Okay, so those tables are not (directly) related.  I would call them independent fact tables.

I had a stray paren somewhere, I would think this:

Total Shipped Value := SUMX (
    REV_HistoryReport,
    REV_HistoryReport[Total Li Valu Amt] / REV_HistoryReport[Ord Bill Unit Qty]) * OpenOrders[Qty Filled]

So, take the avg $ amt (total amt / unit quantity) ... and multiple by the qty of filled orders?   That reads ok.

[Kimbornhoft]

Hello,

Thanks so much for replying. This is the error message that I get when I use that formula.

A single value for column 'Qty Filled' in table 'OpenOrders' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.

[Kimbornhoft]

[Kimbornhoft]

We thought it would work to create a relationship between the open orders and the Rev_HistoryReport based on the MMMID but we get this error message.

[Kimbornhoft]

Is there anything else you need to understand the relationships. Thanks for your help. I'm a novice at Power BI so your help is very much appreciated.

[Kimbornhoft]

Here are the relationships