Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi.
I have a Matrix with 4 columns as follows:
Line Of Business - Current Year - % of Total - % Division
Liability - $47,993,349 - 63% - 63%
Property - $27,765,694 - 36% - 36%
Umbrella - $358,047 - 0% - 0%
Total - $76,117,090 - 100% - 100%
When I filter by a specific agent I get:
Line Of Business - Current Year - % of Total - % Division
Liability - $661,413 - 49% - 1%
Property - $695,683 - 51% - 1%
Umbrella - $2,826 - 0% - 0%
Total - $1,359,922 - 100% - 2%
The FACT Table is the source for the Current Year value.
The FACT table is filtered by an Agent Dimension and both are joined on AgentCode.
I need the % Division field to not change when the Agent Filter is applied (in fact when any dimension filter is applied).
Here is my DAX but the ALL() does not ignore the filters.
% Division = DIVIDE(SUM('Agency Scorecard 3a'[Business DWP]), CALCULATE(SUM('Agency Scorecard 3a'[Business DWP]), ALL()),0)
Can anyone help?
Solved! Go to Solution.
Right now you only remove filters for the denominator, for nominator you want to remove all filters except for the Line of Business
e.g. like this
% Division = VAR __Nominator = CALCULATE ( SUM ( 'Agency Scorecard 3a'[Business DWP] ), ALLEXCEPT ( 'Agency Scorecard 3a', 'Agency Scorecard 3a'[Line Of Business] ) ) VAR __Denominator = CALCULATE ( SUM ( 'Agency Scorecard 3a'[Business DWP] ), ALL () ) RETURN DIVIDE ( __Nominator, __Denominator, 0 )
you may need to adjust the table name for the nominator
ALL works exxectly as designed here - it does remove all the filters, including the one you put on the specific agent
it basically does
661,413/7,611,709 ~= 1%
agent specific sales divided by total sales
rather than what you want
661,413/1,359,922 ~=49%
agent specific sales divided by agent specific total sales
you may want to use ALLSELECTED or ALLEXCEPT instead
also you can have a read here
https://www.sqlbi.com/articles/managing-all-functions-in-dax-all-allselected-allnoblankrow-allexcept...
https://www.sqlbi.com/articles/the-definitive-guide-to-allselected/
Hi.
Thanks for your response but what I want is for % Division to stay at 63% & 36% regardless of what filter is applied, in this case agent.
Is there a way to ignore all filters or specify what filters to ignore in order to satisfy my requirement?
I've tried ALL, ALLSELECTED and ALLEXCEPT
Thanks.
Right now you only remove filters for the denominator, for nominator you want to remove all filters except for the Line of Business
e.g. like this
% Division = VAR __Nominator = CALCULATE ( SUM ( 'Agency Scorecard 3a'[Business DWP] ), ALLEXCEPT ( 'Agency Scorecard 3a', 'Agency Scorecard 3a'[Line Of Business] ) ) VAR __Denominator = CALCULATE ( SUM ( 'Agency Scorecard 3a'[Business DWP] ), ALL () ) RETURN DIVIDE ( __Nominator, __Denominator, 0 )
you may need to adjust the table name for the nominator
That worked - you're a genius!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
39 | |
20 | |
19 | |
16 | |
15 |
User | Count |
---|---|
49 | |
26 | |
22 | |
17 | |
16 |