Calculate total sales of baskets that contain one item

Power BI
1

Hi everyone

I’m a beginner at this and am stuck on something that I’m sure is pretty simple.

I’ve got a basic model set-up with a posted sales transactions table, date table, and product table.

I want to be able to calculate the sales (and also AOV, margin etc) on all orders that contain Product X, not just Product X sales.

Thanks very much

2

@mazhowitt,

Welcome to the forum - great to have you here! This is a pretty straightforward question, but the specific answer will depend on how your sales table is structured. If you could please post your PBIX file, that would allow us to provide you the best support. If your file contains sensitive or confidential info, here’s a video I put together with some simple techniques for masking sensitive info to allow you to post it:

Thanks!

  • Brian
3

Example.pbix (508.5 KB)

4

Thank you so much Brian, appreciate it, attached is PBIX file.

5

@mazhowitt,

Perfect - thanks!. I’ve got to duck out for an hour or so, but will have a solution for you later this afternoon.

  • Brian
6

Thank you, absolutely no hurry.

8

I should have said that my original datasets are huge, so I think a calculated column isn’t workable.

9

Hi @mazhowitt

Try this measure

Total Sales Orders =
VAR _Item =
    SELECTEDVALUE ( 'Item'[Item No] )
VAR _Filter =
    FILTER (
        'Posted Sales Transactions',
        'Posted Sales Transactions'[Item No] = _Item
    )
VAR _Table =
    SUMMARIZE ( _Filter, 'Posted Sales Transactions'[Document No] )
VAR _Result =
    CALCULATE (
        [Total Sales],
        ALL ( 'Posted Sales Transactions' ),
        'Posted Sales Transactions'[Document No] IN _Table
    )
RETURN
    _Result
10

@mazhowitt,

OK, @jbressan got in quicker, but because it’s always valuable to see how different people addressed the same problem, here’s my solution. I used a disconnected table slicer on Item # (and instead of a standard slicer, I used the custom SmartFilter Pro - a custom visual I really like that handles this sort of data really well IMO). Then, here’s the measure that does most of the heavy lifting:

Customers Who Bought = 

//Filters the Sales Table Down to Purchases of the Selected Items
VAR CustTable =
    CALCULATETABLE(
        'Posted Sales Transactions',
        FILTER( 'Posted Sales Transactions', 
            [Bought Item] = 1
        )
    )

//Creates a 1-Column Table from CustTable of Customers Who Purchased
// One or More of the Selected Items
VAR CustList =
    SELECTCOLUMNS(
        CustTable,
        "@customers", 'Posted Sales Transactions'[Sell-to Customer No]
    )

//If the Selected Customer in the Sales Table is in the list of Customers
//who purchased the selected item(s), measure gets a 1, otherwise gets 0
VAR CustInCustList =
    IF(
        SELECTEDVALUE( 'Posted Sales Transactions'[Sell-to Customer No] ) IN CustList,
        1,
        0
    )
    
RETURN
    CustInCustList

And here’s how it looks all put together:

image
image632×531 23 KB

I hopt this is helpful. Full solution file posted below.

11

Thank you both so much, really appreciate it.

1 Like
12

Hi mazhowitt, I may be late to the party, but I do have a solution worked out. I created a new measure of calculating order level sales where the order has selected product in slicer.

Total Order Sales =
var _dn = values(‘Posted Sales Transactions’[Document No])
return
CALCULATE(sum(‘Posted Sales Transactions’[Sales Amount]), filter(ALL(‘Posted Sales Transactions’), ‘Posted Sales Transactions’[Document No] in _dn))

Also created Total Order Cost in very similar way. The other measures were branched from the two. One example is with Product 6292, DRDI3937487 has 3 items so total is the order value is $4,179, as opposed to just $1393 for that product.

See if this helps.
Example-ans.pbix (514.6 KB)

1 Like