Need help with a measure to divide the sum of a column of averages by a different column.

[mlynaugh]

Hey guys, I am on this page all the time finding solutions, but this is the first time I am truly stumped and am asking for help. I have a weird situation,

I manage our freight database and finished building a Power BI report/dashboard for our freight data and I am stuck on one final item that I thought should be simple.

The situation I am in is that I created a tab that is calculating the percentage of our freight costs that have been recovered. The report is based off of an order number, it should be as simple as divide the freight charge we billed the customer by the freight charge we got billed by the shipping company. However, the difficulty comes in that one order can be shipped from multiple locations, or at multiple times. So, when we bill the customer they receive one freight charge (calculated at the time of the order entry), but we get billed for each actual shipment by the trucking companies. When you look at the database this causes duplicate data.

I have included a sample that I created in Excel so you can see my problem.  In this example, order # 123456 is being shipped to NY.  We billed the customer $125 for shipping.  As you can see however, there were 3 separate shipments that are associated with this order, and the total cost of the actual freight was $325.  In order to get around this issue for the table on the BI report, the "Freight Invoiced Cust" field is an avg (since all rows are the same thing), and I have a subtotal of all the averages that shows on the table.

I somehow need to get a formula (I'm assuming measure) that can calcuate the subtotal of all those averages ($125 in this example) by the actual subtotal of "Bill by Shipping Co".

Thank you in advance for your help with this, a couple of us here have been trying this for days and are coming up blank.

FYI, the final answer will just be a text box above the table so the user can quickly see the % of freight cost recovered by whatever filter they apply.

(I grayed the numbers that are duplicate values so you can see what I mean)

[AkhilAshok · Accepted solution]

A quick solution would be something like this:

% of Frieght Recovered =
VAR TotalBilledByShippingCo =
    SUM ( 'Table'[Billed By Shipping Co] )
VAR CustFrieghtInvoice =
    SUMX (
        SUMMARIZE ( 'Table', 'Table'[Order #], 'Table'[Frieght Invoiced Customer] ),
        'Table'[Frieght Invoiced Customer]
    )
RETURN
    DIVIDE ( CustFrieghtInvoice, TotalBilledByShippingCo )

Performance can get bad, if the table is big. A proper appraoch would be create seperate table for Frieght Invoiced Customer, which is one row per Order#, so that you can simply do SUM, without worrying about the duplication.

[mlynaugh]

Thank you very much for your quick reply.  I have tried the separate table, but for some reason it will only let me select one column (Order No), but when I try to get the freight billed it won't let me create a new table anymore.

I just tried your formula and it is close, but for some reason the math is slightly off.  I'm not sure why.  The actual math is off roughly 4% from what this formula produced, and I am stumped as to why.  Your formula is 4% lower than actual. 

I am using this formula for the sum of the averages....I believe it works correctly:

Cust Invoiced Frgt Amt =
var CustInvoicedFrgtAmt = averagex(FreightExpense,FreightExpense[FreightCharges])

RETURN
if( HASONEVALUE(FreightExpense[OrderNo] ),
CustInvoicedFrgtAmt,
sumx(values(FreightExpense[OrderNo]),CustInvoicedFrgtAmt))

Do you have any ideas?

Thanks.

Mike

[AkhilAshok]

Can you give me the dataset where it is not working and the expected value?

[mlynaugh]

I'm stuck on that, since this is for work, I can't share that.   Actually, the dataset is 135MB file, when I export it, so that even complicates it. 

I am curious, the formula you gave, that's to go into a new measure, correct?  Not column? 

[mlynaugh]

I am going through the data and trying to pull a smaller subset to show you, and your formula is perfect for every small sample I look at.  When I unfilter everything and show all my data the numbers don't match.  I'm confused why it is correct for small data sample, and off for the larger.  That says to me something might be wrong in my numbers with the subtotal, but I'm not sure.

[mlynaugh]

I have confimed that my formula that sums up the averages is incorrect.  Once I replaced that data (in Excel) it matches your percentage perfectly.

Thank you very much for your help!