Hello DNA Community,
What I’m trying to do is to calculate the % of Costs (by categorie, Salaries, Marketing,…) of Net Sales inside a Matrix by the different markets we have. See Excel attach.
The model to help understand the calculations
1st: Net Sales is a calculation I have to do using the “account table” that provides me all the necessary informations to do this. Gross Sales(Accounts LT column) - Discounts (Accounts LT column) = Net sales
2nd: I have to do divide the Expenses buy the Net Sales to get the % of expenses by Category (Salaries, Marketing,…) comapring to Net Sales.
The issue I m facing is that since Net sales is calculated using filters from the account table, when I want to display my formula saying Actuals/ Net Sales = % costtoNetSales it uses the initial filter placed to calculate NetSales, But in a context of Marketing expenses, salaries,… I get no NetSales (since its beeing filtered by Salaries, marketing,…) and in a context of Net sales I get no Marketing expenses (since the same table is beeing filtered this time by GrosseSales - Discounts).
Do you know if there is a way to FIX this Net Sales Measure so it won’t be affected buy a any given context?
Thanks for you help
Regards
Mark
Hello Mark,
You need to do an ALL or ALLEXCEPT function to get your % of Net Sales.
I believe you could do
% of Net Sales = CALCULATE( [SalariesMeasure] / [NetSales] , ALLEXCEPT(‘Markets’[Country]) )
I’m newer to using the ALL functions, but this in theory should remove other filter context like slicers and Row Level where it filters by Salaries, Marketing, etc, but it will keep the filter context applied my Market. That should mean it takes your Salaries in France of 200 because you are using your measure for Salaries and dividing it by the NetSales that will still filter by Market or Country (whatever you have it named as).
I hope this works for you.
Hello Weaber,
Thanks you for your reply. The problem I face with what you suggest is that when using All or allexcept or all selected is that it reomves the filters from calculation that allows me to get the Net Sales + The visual is using the same Table&coloumn has the one used to calculate the the Net Sales. Its a mess to figure out how to do this. The solution I found was to import an Excel template and place every single forumla in front of the accounts using the Switch(TRUE() function, but this solution requires a lot of “repetitive DAX”.
Regards
Mark
Hello Mark,
Any chance you could share you model? I’m sure there is a way to utilize one of the All functions.
Even if it requires a Switch for just ‘Salaries’ and ‘Marketing’ to specify one of the filters to retain.
Andrew
