Wrong Total in SUMX

[MohamedSalih_12]

I have two fact tables 

1. Historic Stock prices downloaded from Google sheets with 3 Columns Date, Ticker, Unit Price

2. Stock Buy transactions table with 4 Columns Date, Ticker, Quantity, Unit Price

3. Below is the matrix which contains Date from Calendar Table, Ticker from Bridge Table, Quantity from Transaction table, Last Unit Price from Historical Prices Table with REMOVEFILTERS('Calendar'[Date] applied, all is well so far

The issue is with the measure [Amount 2] = SUM('Transaction'[Quantity])* [Last Unit Price]. It works well in the row context of Tickers, but in the context of Date (see 5/7/2024), it multiplies total Quantity with total of the Unit Prices (i.e 392,036.76).

But I want in the Date rows summation of product of each ticker which has to be (95,027.12)

I hope I explained the situation well, looking for a corrected measure.

[v-nuoc-msft]

Hi @MohamedSalih_12 

Thank you very much lbendlin for your prompt reply.

Can you tell me if your problem is solved? If yes, please accept it as solution.

Regards,

Nono Chen

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

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.

Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[MohamedSalih_12]

Thank you for your reply

Somehow, I managed to find a solution for the yesterday's problem. Now I am stuck with another one, please find below

I am trying to make a matrix with 
Calendar[Date],   Data[Ticker] and I want to add a measure to return the LAST PRICE for each ticker from Historical Prices table in all the Calendar[Date] rows. Also, it must return Blank if that ticker has no transaction in Transaction table in that Date

I managed to achieve the required result with helper table, please help to achieve the same result without the helper table and see the attached pbix file.  https://drive.google.com/file/d/1pvIsF2q8_2tp9Wrj8sS6drFvkvS7VWj4/view?usp=sharing 

My apologies if I could not explain the problem well, please feel free to ask if anything

[lbendlin]

[MohamedSalih_12]

the below is the expected result after modifying your DAX slightly. It works well if the 'Calendar' and 'Historical_Prices (fact)' tables are disconnected. Once the relationships are made, it does not work as expected. 

I want to know how to make it work while the relationships are active, because I need the relationship for other calculations

[lbendlin · Accepted solution]

Make the relationship inactive. Use USERELATIONSHIP in your measure.