Forum Discussion

duncanUK's avatar
duncanUK
Frequent Visitor
5 years ago
Solved

Adding calculated total

I'm quite new to DAX and I'm struggling to add a measure, which I thought should be straightforward, but despite a lot of searching online and testing, I can't achieve the desired result. Any guidanc...
Need Help
  • Jihwan_Kim's avatar
    Jihwan_Kim
    5 years ago

    Hi,

    Sorry that I kept misunderstanding your point.

    I am not quite sure how to display the numbers in the table visual and the card visuals.

    please check the below link if it is OK with you.

    I think the numbers in card visuals are correct. 

    If you want to fix the numbers in the table visual, please let me know how to describe it.

    Thank you.

     

    https://www.dropbox.com/s/h9v89dyyj3fsnty/duncan.pbix?dl=0 

duncanUK's avatar
duncanUK
Frequent Visitor
5 years ago

Hi Jihwan,

 

Thanks for your response. I was trying to get the two cards in your example to show:

1) The total sales which have occurred in stores with no budget = £10 in this example

2) The total budget in stores with no sales = £35 in this example

 

Thanks you again for looking at this.

Jihwan_Kim's avatar
Jihwan_Kim
Super User
5 years ago

Hi,

Thank you for your feedback.

Sorry that I was misunderstanding your question.

Please try the below two measures for the two card visualizations.

 

Store Sales =
CALCULATE( [Sales], FILTER(Sales, [Budget] = 0))
 
Store Budget =
CALCULATE( [Budget], FILTER( Sales, [Sales]=0))
 

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

  • duncanUK's avatar
    duncanUK
    Frequent Visitor
    5 years ago

    Hi,

     

    Sorry to say but that's not working, as it includes rows from stores where the store level sales or budget > 0, e.g. in this example the measures include rows from store 1:

     

     

    Thanks,

    Duncan

    • Jihwan_Kim's avatar
      Jihwan_Kim
      Super User
      5 years ago

      Hi,

      I thought you were only going to create Card Visualizations for those.

      Once again, sorry for my misunderstanding.

       

      Please check the below measures and link.

       

      Store Sales =
      CALCULATE( [Sales], FILTER(ALLEXCEPT(Sales, Sales[Store]), [Budget] = 0))
       
      Store Budget =
      CALCULATE( [Budget], FILTER(ALLEXCEPT('Sales', 'Sales'[Store]), [Sales]=0))
       
       

      Hi, My name is Jihwan Kim.

      If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

      • duncanUK's avatar
        duncanUK
        Frequent Visitor
        5 years ago

        Hi

         

        Yes I was trying to display them in a card, but the values on the cards are still not as expected, hence I also added to the grid to fault find. Note I've changed the data for store 1 from your pbix file.

         

        The total budget in stores with no sales should be £35

        The total sales in stores with no budget should be £10

         

        In both cases it's incorrectly including rows from store 1, which has both sales and budget at the store level.

         

        Thank you again,

        Duncan