Forum Discussion
Wrong Total in SUMX
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.
Make the relationship inactive. Use USERELATIONSHIP in your measure.
6 Replies
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-Forum/ba-p/963216
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523Thank 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 DateI 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
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.
