Totals are incorrect

[scott42]

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!

[vivran22 · Accepted solution]

Hello @scott42 

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

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

[scott42]

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

[v-janeyg-msft]

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
)

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