Forum Discussion
Sub-items don't add up to total
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???
8 Replies
- BeaBF
Super User
Anonymous Hi! Can you paste some sample data on which you calculate the measure? and the screen of the output/expected output.
BBF
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/totalsBBF
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.
