Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I'm having issues trying to calculate a total in one table that is related to several tables and I am trying to use some filters and ignore others in my total.
I have a page level filter based on certain products (table A). Secondly, I have a slicer by geography (table B). My data for total sold is in table C. Table C is related to Table A and B.
I'm trying to calculate total sales from Table C, whereby I'm ignoring the product filter in Table A to include all products, but I want to keep the geography filter in Table B
My equation is MYTOTAL = CALCULATE(sum('Table C'[Total]),ALLEXCEPT('Table C', 'Table C'[Geography Tie Code]))
The column Geography Tie Code connects to table B's column that filters product by page view.
The equation works for including all products; however, when I include filters in the geography slicers, I'm not able to get the total for just the selected geography - it's including all geographies.
Any thoughts to adjust the equation? Thanks!
Solved! Go to Solution.
Can you clarify this a bit and maybe post some relevant sample data? You have a page filter for products Table A but then you want to ignore it? Can you just get rid of it or is it useful for other visualizations on the page.
I believe the issue with your formula is that your ALLEXCEPT needs to refer to Table B, what your slicer is based off of. I *think* that is why it is not working.
If you look at the example for ALLEXCEPT:
https://support.office.com/en-US/article/ALLEXCEPT-Function-DAX-f31a3742-bafe-4af3-9c24-f08439eca85d
The ALLEXCEPT clause refers to the DateTime table, not the summing table,
Can you clarify this a bit and maybe post some relevant sample data? You have a page filter for products Table A but then you want to ignore it? Can you just get rid of it or is it useful for other visualizations on the page.
I believe the issue with your formula is that your ALLEXCEPT needs to refer to Table B, what your slicer is based off of. I *think* that is why it is not working.
If you look at the example for ALLEXCEPT:
https://support.office.com/en-US/article/ALLEXCEPT-Function-DAX-f31a3742-bafe-4af3-9c24-f08439eca85d
The ALLEXCEPT clause refers to the DateTime table, not the summing table,
User | Count |
---|---|
139 | |
113 | |
103 | |
77 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |