Search code examples
powerbidax

Create custom columns and append to matrix

I want to create a custom matrix based on multiple tables

Given a dataset like this:

Counterparty  Product  Deal  Date          Value
foo           bar      Buy    01/01/24     10.00
foo           bar      Buy    01/01/24     10.00
foo           bar      Sell   01/01/24     10.00
foo           bar      Sell   01/01/24     10.00
fizz          bar      Buy    01/01/24     10.00
fizz          bar      Buy    01/01/24     10.00
fizz          buzz     Sell   01/01/24     10.00
fizz          buzz     Sell   01/01/24     10.00

I create a matrix like this:

Counterparty  Bar  Buzz  Total 
foo           40    0      40   
fizz          20    20     40
Total         60    20     80

using Counterparty as rows, Product as columns, and TotalValue as values:

--Create Table in model

Combinations = 
CROSSJOIN(
    VALUES('table1'[Counterparty]),
    VALUES('table1'[Product])
)

--Create Measure for calculated totals

TotalValue = 
VAR CurrentCounterparty = SELECTEDVALUE(Combinations[Counterparty])
VAR CurrentProduct = SELECTEDVALUE(Combinations[Product])
VAR TotalValue = CALCULATE(
    SUM('table1'[Value]),
    'table1'[Counterparty] = IF(HASONEVALUE(Combinations[Counterparty]), CurrentCounterparty, 'table1'[Counterparty]),
    'table1'[product] = IF(HASONEVALUE(Combinations[Product]), CurrentProduct, 'table1'[Product])
)
RETURN IF(ISBLANK(TotalValue), FIXED(0, 0), TotalValue)

I also have a similar table with different values that I need to create the custom columns with:

Counterparty  Product  Deal  Date          Value
foo           bar      Buy    01/01/24     11.00
foo           bar      Buy    01/01/24     09.00
foo           bar      Sell   01/01/24     09.00
foo           bar      Sell   01/01/24     10.00
fizz          bar      Buy    01/01/24     12.00
fizz          bar      Buy    01/01/24     08.00
fizz          buzz     Sell   01/01/24     09.00
fizz          buzz     Sell   01/01/24     10.00

The final matrix should look like this:

Counterparty  Bar  Buzz  Total  col1 col2
foo           40    0      40    39    1
fizz          20    20     40    39    1
Total         60    20     80    78    2

I figure I have to create a new table out of the two tables so that have the correct values to populate my columns and then create measures accordingly, but I am not sure how to proceed. Or I am open to hearing suggestions for a different approach.

EDIT: I was using this as reference but in the example they are creating everything in excel and I do not want to approach like that since I need to populate the Products dynamically using my tables.

Solution

  • The Combinations Table would need to include Table2 values but remove any duplicates:

    Combinations = 
VAR t1 = CROSSJOIN(
    VALUES('Table1'[Counterparty]),
    VALUES('Table1'[Product])
)
VAR t2 = CROSSJOIN(
    VALUES('Table2'[Counterparty]),
    VALUES('Table2'[Product])
)
VAR u1 = UNION(
    t1
    ,t2
)
RETURN DISTINCT(u1)

    Your c1 measure would mirror the TotalValue measure but point to Table2 instead:

    c1 = 
VAR CurrentCounterparty = SELECTEDVALUE(Combinations[Counterparty])
VAR CurrentProduct = SELECTEDVALUE(Combinations[Product])
VAR TotalValue = CALCULATE(
    SUM('Table2'[Value]),
    'Table2'[Counterparty] = IF(HASONEVALUE(Combinations[Counterparty]), CurrentCounterparty, 'Table2'[Counterparty]),
    'Table2'[product] = IF(HASONEVALUE(Combinations[Product]), CurrentProduct, 'Table2'[Product])
)
RETURN IF(ISBLANK(TotalValue), FIXED(0, 0), TotalValue)

    The c2 measure would then subtract c1 from TotalValue:

    c2 = [TotalValue] - [c1]

    Then c1 and c2 can be added to the Values of your matrix.

    Output Matrix

    (I recommend moving the Product to the Rows section of your matrix, to have a cleaner look.)

    Edit: Keeping Bar and Buzz as columns

    Add x amount of measures for each product:

    Bar = 
VAR CurrentCounterparty = SELECTEDVALUE(Combinations[Counterparty])
VAR TotalValue = CALCULATE(
    SUM('Table1'[Value]),
    'Table1'[Counterparty] = IF(HASONEVALUE(Combinations[Counterparty]), CurrentCounterparty, 'Table1'[Counterparty]),
    'Table1'[product] = "bar"
)
RETURN IF(ISBLANK(TotalValue), FIXED(0, 0), TotalValue)

    Buzz = 
VAR CurrentCounterparty = SELECTEDVALUE(Combinations[Counterparty])
VAR TotalValue = CALCULATE(
    SUM('Table1'[Value]),
    'Table1'[Counterparty] = IF(HASONEVALUE(Combinations[Counterparty]), CurrentCounterparty, 'Table1'[Counterparty]),
    'Table1'[product] = "buzz"
)
RETURN IF(ISBLANK(TotalValue), FIXED(0, 0), TotalValue)

    Convert Matrix to Table and add Product measures to table:

    Table Output