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

[duncanUK]

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]

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]

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]

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))

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.

[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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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