Forum Discussion
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 guidance would be very much appreciated!
I have a table with budgets and sales by store and department, like this:
| Store | Dept | Date | Budget | Sales |
| 1 | A | 01/04/2021 | £10 | £10 |
| 1 | B | 01/04/2021 | £20 | £10 |
| 1 | C | 01/04/2021 | £5 | £10 |
| 2 | A | 01/04/2021 | £0 | £10 |
| 2 | B | 01/04/2021 | £0 | £0 |
| 2 | C | 01/04/2021 | £0 | £0 |
| 3 | A | 01/04/2021 | £10 | £0 |
| 3 | B | 01/04/2021 | £20 | £0 |
| 3 | C | 01/04/2021 | £5 | £0 |
Within reports there are times I need to show the total sales in stores without any budgets (e.g. £10 in store 2), and total budget in stores without any sales (e.g. £35 in store 3).
On reports with tables showing the totals by store, it was simple to add a filter to the sales and budget columns, as this was applied at store level. However this didn't work on cards showing the company total, since the filtering was applied to the row level, rather than store level, so was including/excluding rows from store 1.
My thinking was to add measures for the total store sales and budgets, then I could filter on these values:
| Store | Dept | Date | Budget | Sales | Store Budget | Store Sales |
| 1 | A | 01/04/2021 | £10 | £0 | £30 | £20 |
| 1 | B | 01/04/2021 | £20 | £10 | £30 | £20 |
| 1 | C | 01/04/2021 | £0 | £10 | £30 | £20 |
| 2 | A | 01/04/2021 | £0 | £10 | £0 | £10 |
| 2 | B | 01/04/2021 | £0 | £0 | £0 | £10 |
| 2 | C | 01/04/2021 | £0 | £0 | £0 | £10 |
| 3 | A | 01/04/2021 | £10 | £0 | £35 | £0 |
| 3 | B | 01/04/2021 | £20 | £0 | £35 | £0 |
| 3 | C | 01/04/2021 | £5 | £0 | £35 | £0 |
I can't figure out how to add the Store Budget and Store Sales measures, I was trying the following but this still doesn't work when the Store isn't included on the visual:
Store Sales = CALCULATE(SUM(Sales[Sales]), ALLEXCEPT(Sales, Sales[Store]))
Any help is appreciated
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.
49 Replies
Hi, duncanUK
Please correct me if I wrongly understood your question.
Please check the below picture and the sample pbix file's link down below whether it is what you are looking for.
Or, please kindly advise or draw about how your desired outcome looks like.
https://www.dropbox.com/s/h9v89dyyj3fsnty/duncan.pbix?dl=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.
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.
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.
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
