4

Below is a simplified version of transaction data for stocks.

StockData = 
DATATABLE (
    "STOCK", STRING,
    "Date", DATETIME,
    "Buyer", STRING,
    "Seller", STRING,
    "Turnover", INTEGER,
    {
         { "AAPL", "2019/04/07", "GSI", "BRC", 100 },
         { "AAPL", "2019/04/07", "CITI", "JPM", 500 },
         { "AAPL", "2019/04/07", "JPM", "GSI", 700 },
         { "AAPL", "2019/04/08", "GSI", "JPM", 300 },
         { "AAPL", "2019/04/08", "GSI", "CITI", 800 },
         { "AAPL", "2019/04/08", "JPM", "BRC", 400 },
         { "MSFT", "2019/04/07", "GSI", "GSI", 500 },
         { "MSFT", "2019/04/07", "JPM", "BRC", 700 },
         { "MSFT", "2019/04/07", "BRC", "GSI", 800 },
         { "MSFT", "2019/04/08", "GSI", "BRC", 500 },
         { "MSFT", "2019/04/08", "GSI", "JPM", 600 },
         { "MSFT", "2019/04/08", "CITI", "BRC", 500 }
    }
)

Goal is to calculate net turnover by broker per day.

I can achieve this by following DAX measure

Test BRC Net Turnover = 
VAR TotalBuy = CALCULATE(SUM(StockData[Turnover]),StockData[Buyer] = "BRC")
VAR TotalSell = CALCULATE(SUM(StockData[Turnover]),StockData[Seller] = "BRC")
Return TotalBuy - TotalSell

However, to get net turnover for all (Four) brokers in example data above I have to rewrite the measure four times with different critera, i.e., rather than using "BRC" I have to use "GSI" etc...

Real data set consists of 50 different broker codes so the solution with 50 different measures is not feasible.

How can I make this DAX function iterate over all Broker codes in my data set. Taking into consideration filters for the stock. I.e., if a typical broker code doesnt exist for MSFT but for AAPL the measure will take that into consideration.

I have tried the values function to return a list of all brokers, without any success.enter image description here

Above is what I would like to achieve with one single measure.

Many thanks

Adni
  • 133
  • 1
  • 11

1 Answers1

2

Exemplary well-written question!

To do this, let's first create an independent calculated table to use for the different buyers and sellers.

Brokers = VALUES( StockData[Buyer] )

Now we can put Brokers[Buyer] in the Legend field and write a measure that reads that value.

Net Turnover =
VAR Broker = SELECTEDVALUE ( Brokers[Buyer] )
VAR TotalBuy = CALCULATE ( SUM ( StockData[Turnover] ), StockData[Buyer] = Broker )
VAR TotalSell = CALCULATE ( SUM ( StockData[Turnover] ), StockData[Seller] = Broker )
RETURN
    TotalBuy - TotalSell

Chart with Legend

Alexis Olson
  • 38,724
  • 7
  • 42
  • 64
  • 1
    I'd probably do this as `AVERAGEX ( 'Brokers', [TotalBuy] - [TotalSell] )`, where [TotalBuy] and [TotalSell] are simple `SUM`s of the respective fields. This would give identical results whenever 'Brokers'[Buyer] is in the current context (like in your sample visual), but would give a per-buyer average at a total level. This would allow an easy way to compare buyers with something like `Buyer Turnover Difference from Average = [Net Turnover] / CALCULATE ( [Net Turnover], ALL ( 'Brokers' ) )`. – greggyb Aug 19 '19 at 21:57
  • Thanks for the comment @greggyb where "[TotalBuy] and [TotalSell] are simple SUMs of the respective fields." The only field we can summarize in the sample above is the turnover field. Perhaps I misunderstood what you meant? – Adni Aug 20 '19 at 10:02
  • 1
    Thank you Alexis solution works as intended. Just one addition: Assume for one stock you only have GSI Selling. Then: `Brokers = VALUES( StockData[Buyer] )` Would not include GSI. I solved this by using the following formula: `DISTINCT(UNION(VALUES(StockData[Buyer]),VALUES(StockData[Seller])))` – Adni Aug 20 '19 at 10:08
  • Apologies, Adni, I focused on the names of the measures you had written, rather than the schema of the table. I was thinking of buyer and seller being pivoted. That detail is less relevant than the structure of using `AVERAGEX`, which will give the totaling properties I discussed. – greggyb Aug 22 '19 at 15:17