Sub-items don't add up to total

[Anonymous]

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

[Anonymous · Accepted solution]

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.

[Anonymous]

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.

[Anonymous · Accepted solution]

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.

[Anonymous · Accepted solution]

Hi @Anonymous ,

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

Best Regards,
Wenbin Zhou

[BeaBF]

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

BBF

[Anonymous]

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

[BeaBF]

@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

[Anonymous]

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.

[BeaBF]

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

BBF