Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
scott42
Frequent Visitor

Totals are incorrect

Hi,

I am having trouble with totals showing incorrect amount.

I have a table with a transaction list of stock purchases/movements and a table of daily stock close prices.
I want to show the total value over time.
I can calculate the cumulative quantity * the daily close prices to show on a daily basis what the value of each stock is, this works fine.

When I try to see the total value of all stocks this is where I run into problems and the total values are not adding up.

I have tried multiple measures including SUMX but I must be missing something.

Link to the .pbix file I am working off - https://drive.google.com/file/d/1_DHrVm0CElQtKkr34dQa_xAwV7BpU6yv/view?usp=sharing

 

Some help would be much appreaciated, thanks!

1 ACCEPTED SOLUTION
vivran22
Community Champion
Community Champion

Hello @scott42 

 

It is not clear from your description about the output requirement, but is this what you are looking for?

 

vivran22_0-1633944296135.png

 

If yes, then you may use the following measure:

 

Summary DAX =
VAR _Table =
SUMMARIZE('Share-Crypto - Prices',
'Date Table'[Date],
'Share-Crypto - Ticker Table'[Ticker],
"@Qty", [Quantity (Cumulative)],
"@ClosePrice",[Close Price],
"@Value",[Value]
)
VAR _Output = SUMX(_Table,[@ClosePrice])
RETURN
_Output
 
 
Cheers!
Vivek

If it helps, please mark it as a solution
Kudos would be a cherry on the top 🙂 (Hit the thumbs up button!)
If it doesn't, then please share a sample data along with the expected results (preferably an excel file and not an image)


https://www.vivran.in/

Connect on LinkedIn

View solution in original post

3 REPLIES 3
vivran22
Community Champion
Community Champion

Hello @scott42 

 

It is not clear from your description about the output requirement, but is this what you are looking for?

 

vivran22_0-1633944296135.png

 

If yes, then you may use the following measure:

 

Summary DAX =
VAR _Table =
SUMMARIZE('Share-Crypto - Prices',
'Date Table'[Date],
'Share-Crypto - Ticker Table'[Ticker],
"@Qty", [Quantity (Cumulative)],
"@ClosePrice",[Close Price],
"@Value",[Value]
)
VAR _Output = SUMX(_Table,[@ClosePrice])
RETURN
_Output
 
 
Cheers!
Vivek

If it helps, please mark it as a solution
Kudos would be a cherry on the top 🙂 (Hit the thumbs up button!)
If it doesn't, then please share a sample data along with the expected results (preferably an excel file and not an image)


https://www.vivran.in/

Connect on LinkedIn

Hi @vivran22,

 

Thanks for your solution, with a minor tweak this has worked for me.
The solution I wanted to get to was that on any given day I could see the total value of all shares (or multiple selected) and I can now see the correct totals on the graph.

I have a follow up question which is related (apologise if this is the incorrect thing to do by posting it here) - 
I have uploaded the latest .pbix file.

You will notice that on the 25th and 25th September there are no 'Close Price' due to it being a weekend. Do you know how to show Fridays close price on the weekend days and then also continue calculating the value? hope that makes sense? 
latest file - https://drive.google.com/file/d/1epXnW1XvJI7I_GoS1YAqVAbz1O0ZvuZn/view?usp=sharing

scott42_0-1634065314261.png

 

 

Hi, @scott42 

 

You can use weekday function to filter data, create a measure and use it in filter pane.

Like this:

Measure =
IF (
    WEEKDAY ( SELECTEDVALUE ( 'Date Table'[Date] ), 2 ) = 6
        || WEEKDAY ( SELECTEDVALUE ( 'Date Table'[Date] ), 2 ) = 7,
    0,
    1
)

vjaneygmsft_0-1634111644690.png

Did I answer your question ? Please mark my reply as solution. Thank you very much.
If not, please feel free to ask me.


Best Regards,

Community Support Team _ Janey

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.