topic Re: Sub-items don't add up to total in DAX Commands and Tips

Sub-items don't add up to total

Anonymous — Thu, 18 Jul 2024 03:42:04 GMT

I created a filter 'ReceiptDate' for users to select the range of date in table 'Gross Sales'. How come the following formulae show the same total in matrix, but the rows in the matrix does not add up to the total for DAX formula 2?

DAX formula 1 without filter 'ReceiptDate':

Gross sales (AAA) = CALCULATE([Sum of gross sales],Filter('Gross Sales'), [Types] = "AAA"))

DAX formula 2: with filter 'ReceiptDate':

Gross sales (AAA) = CALCULATE([Sum of gross sales],Filter(All('Gross Sales'[ReceiptDate],'Gross Sales'[Types]), [Types] = "AAA"))

I made two pages for illustrating the issue. The first one includes a slicer for 'ReceiptDate' while the second page does not.

https://drive.google.com/file/d/1JR72zUFIasOJBtIB1sXDH4TjssYAUpQR/view?usp=sharing

Page 1 with the slicer 'ReceiptDate'. The amount does not add up.

Page 2 without the slicer 'ReceiptDate'. The amount adds up to the total.

Some items disappear because they are named in Chinese???

Re: Sub-items don't add up to total

BeaBF — Wed, 17 Jul 2024 12:30:03 GMT

Anonymous Hi! Can you paste some sample data on which you calculate the measure? and the screen of the output/expected output.

BBF

Re: Sub-items don't add up to total

Anonymous — Thu, 18 Jul 2024 03:19:54 GMT

I made two pages for illustrating the issue. The first one includes a slicer for 'ReceiptDate' while the second page does not.

https://drive.google.com/file/d/1JR72zUFIasOJBtIB1sXDH4TjssYAUpQR/view?usp=sharing

Page 1 with the slicer 'ReceiptDate'. The amount does not add up.

Page 2 without the slicer 'ReceiptDate'. The amount adds up to the total.

Some items disappear because they are named in Chinese???

Re: Sub-items don't add up to total

BeaBF — Thu, 18 Jul 2024 07:40:28 GMT

Anonymous Try with this new measure:

Measure_BBF =

SUMX(FILTER('Gross Sales', 'Gross Sales'[Type of spending] = "AAA"), ('Gross Sales'[Receipt_Amount]))
it works with slicers and returns the correct subtotals/totals

BBF

Re: Sub-items don't add up to total

Anonymous — Thu, 18 Jul 2024 07:53:21 GMT

Hi BeaBF ,Thanks for your quick reply, I will add more.

Hi Anonymous ,

Regarding your question, you need to know that DAX calculations are divided into internal filtering environment and external filtering environment.

External:

Internal:

The internal environment will first generate a table (excluding the filtering effect of the slicer)

'Type of spending' = "AAA" (ReceiptDate = 5/11/2024 Loss of filtering effect)

Externel:

The results after filtering by slicer.

These filter contexts filter the tables created previously in the internal environment.

Best Regards,
Wenbin Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Sub-items don't add up to total

Anonymous — Thu, 18 Jul 2024 08:34:18 GMT

Well, thanks for your swift response. I created a measure 'Gross Sales' because I hope this function will not be affected by the slicer 'ReceiptDate'. It does give me the right answer 54,896.5 no matter how I moves the slicer, but the sub-items just don't add up.

Your function is right if you select the date to be Apr 2024. However, it cannot show anything when I choose the date to be 11May2024.

Re: Sub-items don't add up to total

Anonymous — Thu, 18 Jul 2024 08:54:10 GMT

I think I now understand the row items are controlled by the slicer. The measure comes up with the right total value, but the table cannot show the sub-items which are controlled by the slicer.

Re: Sub-items don't add up to total

BeaBF — Thu, 18 Jul 2024 09:45:43 GMT

Anonymous I did not undestand the expected output. Can you explain better to me?

BBF

Re: Sub-items don't add up to total

Anonymous — Mon, 22 Jul 2024 01:56:10 GMT

Hi Anonymous ,

Did you solve your problem? If yes, please mark my answer as the solution.

Best Regards,
Wenbin Zhou