Power BI - DAX measure to reference a list of values. Calculate net turnover per broker - more elegant solution?

4
votes

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

1
listpowerbidaxcalculated-columns

1 Answers

2
votes

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