Multiply values from two tables joined by date and [Symbol] column

[Arcangel786]

Hi guys, 

I am new to Power BI, I have some experience with SQL and Python so I would know how to replicate this with those tools, but I spent a few hours trying to solve this and looking to many similar issues on the web to no avail. Please, can you give me a hand? Here is the problem:

I am creating a Crypto Tracker portfolio, I have a table "Txn_Data" where I store all BTC txns with dates, prices and amount etc, I have another table "BTC_history" with price history that is joined to "Txn_Data" on [Date] columns through dimension table "Calendar" and joined by [Symbol] columns though another table "API_Call"

I created a calculated column Txn_Data[Running Total Token] column to store the amount of BTC accumulated and my idea is to multiply 

Txn_Data[Running Total Token] * BTC_history[Value]

where  BTC_history[Date] =  Txn_Data[Date]

and Txn_Data[Symbol] = BTC_history[Symbol]

I tried this but it doesn't display anything. I am gonna post the data model for you to see the relationships. Can you help me with that?

 

Data model 

[Anonymous]

Hi, @Arcangel786 

Based on the data shown in the description and picture, I took a screenshot of some of the data and created the related table as shown in the pictures below:

In my test, the following results were shown directly using the multiplication of two columns:

I used the following DAX expression, and I got the correct result:

Running total right =
VAR _a =
    FILTER ( 'BTC_History', 'BTC_History'[Date] = 'Txn_Data'[Date] )
RETURN
    [Running Total Token] * CALCULATE ( SUM ( BTC_History[Value] ), _a )

I used filter to filter out a table _a for 'BTC_History'[Date]='Txn_Data'[Date]. I aggregate the BTC_History[Value] fields of this table in calculate, and then multiply the corresponding result by [Running Total Token]. You can click on the link below to learn how to use the filter function: 

FILTER function (DAX) - DAX | Microsoft Learn

FILTER - DAX Guide

How to Get Your Question Answered Quickly

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards

Jianpeng Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Arcangel786]

You are very kind for your comprehensive answer.

I however changed a bit my final requirement so I had to change the original table "BTC_History" for "Coins_History" so instead of just having BTC price history, it will contain multiple crypto price history. I am not sure how is the best way to represent that in the data model design. I opted for creating the "Coins_History" table with these columns [Date], [Symbol] and [Value] where [Value] will contain the price for those crypto.

Columns Coins_History[Symbol] should join the same Values in Column Txn_Data[Symbol] and also be joined by Coins_History[Date] and Txn_Data[Date] 

Coins_History Table

Excpected Result

I will share a pbix file so you can have a look, it is a bit messy though. My intention is to add more Crypto to Coins_History but this is just a sample so far.  https://drive.google.com/file/d/1yU__aCOsojfy4Obxzec63tgY6u5qk296/view?usp=sharing

Summary:

- Best approach to achieve a column/measure to store the portfolio value accros history i.e. Txn_Data[Running Total Token] * (Coins_History[Value] Grouped by Coins_History[Symbol]) for each matching date between Txn_Data and Coins_History

- Why would it better to use Measures to achieve this over Calculated Columns or viceversa?

- Is my data model to represent Coins_History Table appropriate or there is better practices to achieve the same goal when designing the data model?

- Do you know why my measure CurrentPriceAsset doesn't have a common join column when before it was working properly? 

Thanks a lot for your time, really appreciate it

Regards 

Gabriel

[Anonymous · Accepted solution]

Hi, @Arcangel786 

Thank you very much for your reply. Here are my thoughts on a few of your questions:

1.Why would it better to use Measures to achieve this over Calculated Columns or viceversa?

Measures and calculated columns each have their own use cases, and when there is a large amount of data, we may prioritize measures. This reduces the size of the PBIX file. When we need to display data in certain visuals and must use columns, calculated columns may be a better option.

2.Is my data model to represent Coins_History Table appropriate or there is better practices to achieve the same goal when designing the data model?

Coins_History Table is a good example of this.

3.Do you know why my measure CurrentPriceAsset doesn't have a common join column when before it was working properly?

NATURALINNERJOIN function needs an active relationship. Here are the requirements for this function:

When I activate the relationship, this error goes away:

NATURALINNERJOIN function (DAX) - DAX | Microsoft Learn

I use the following DAX to calculate the Running Total Value:

Running Total Value =
VAR _table =
    FILTER (
        'Coins_History',
        'Coins_History'[Date] = 'Txn_Data'[Date]
            && 'Coins_History'[Symbol] = 'Txn_Data'[Symbol]
    )
RETURN
    'Txn_Data'[Running Total Token]
        * CALCULATE ( MAX ( Coins_History[Value] ), _table )

Here are the results:

I've uploaded the PBIX file for this time below, and it would be great if it could help you.

How to Get Your Question Answered Quickly

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards

Jianpeng Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Arcangel786]

Thank you friend for your time and advice! Marked as solution. Have a nice day!

[lbendlin]

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

[Arcangel786]

Thank you Ibendlin. I edited my reply with a link to my pbix file on google drive

[lbendlin]

Looks ok-ish apart from the 1:1 between the calendar and the BTC history.  Make that a 1:* .

For Running Total you can use the Quick Measure template.