Forum Discussion

scott42's avatar
scott42
Frequent Visitor
4 years ago
Solved

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!

Need Help
  • vivran22's avatar
    vivran22
    4 years ago

    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

3 Replies

  • vivran22's avatar
    vivran22
    Icon for Community Champion rankCommunity Champion
    4 years ago

    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's avatar
      scott42
      Frequent Visitor
      4 years ago

      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's avatar
        v-janeyg-msft
        Icon for Community Support rankCommunity Support
        4 years ago

        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