Adding calculated total

[duncanUK]

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

[Syndicate_Admin]

Hello

Yes, I was trying to show them on a card, but the values on the cards are not yet as expected, so I also added them to the grid to look for faults. Note: I changed the data for store 1 of the pbix file.

The total budget in non-sales stores should be £35

Total sales in non-budget stores must be £10

In both cases, it incorrectly includes Store 1 rows, which has sales and budget at the store level.

Thank you again,

Duncan

[Syndicate_Admin]

Hello

Yes, I was trying to show them on a card, but the values on the cards are not yet as expected, so I also added them to the grid to look for faults. Note: I changed the data for store 1 from the pbix file.

The total budget in non-sales stores should be £35

Total sales in non-budget stores must be £10

In both cases, it incorrectly includes rows from Store 1, which has sales and budget at the store level.

Thank you again,

Duncan

[Jihwan_Kim]

Hi,

Please kindly check the data.

I don't think it is the same data.

What you expected is shown in the link.

But, what you are showing by screencapture is different.

Please check the link that I replied to you at the most latest time.

Thank you.

[duncanUK]

Hi,

Yes as meantion I changed the data for Store 1 to show that the solution doesn't work in the situtations where there are rows with sales = 0 or budget = 0, where these are > 0 at store level.

Unfortunately the real life data I'm working with has such rows.

Thanks,

Duncan

[Jihwan_Kim · Accepted solution]

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]

Perfect, thank you!

[duncanUK]

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

[Jihwan_Kim]

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.