topic Re: Wrong Total in SUMX in DAX Commands and Tips

Wrong Total in SUMX

MohamedSalih_12 — Wed, 11 Dec 2024 12:50:29 GMT

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.

Re: Wrong Total in SUMX

lbendlin — Thu, 12 Dec 2024 00:55:39 GMT

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/1447523

Re: Wrong Total in SUMX

MohamedSalih_12 — Thu, 12 Dec 2024 13:48:41 GMT

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

Re: Wrong Total in SUMX

lbendlin — Thu, 12 Dec 2024 21:23:00 GMT

Re: Wrong Total in SUMX

MohamedSalih_12 — Fri, 13 Dec 2024 11:46:36 GMT

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

Re: Wrong Total in SUMX

lbendlin — Fri, 13 Dec 2024 12:39:43 GMT

Make the relationship inactive. Use USERELATIONSHIP in your measure.

Re: Wrong Total in SUMX

Anonymous — Tue, 17 Dec 2024 08:58:55 GMT

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.