I am trying to get a % total for a filter table which shows the top N number of debtors however i cant get the right %s for each customer. Is someone able to assist
First thing I would highly recommend here is break out each part of this formula into seperate measures.
Utilize the measure branching methodology where you can.
https://blog.enterprisedna.co/2018/08/01/what-is-measure-branching/
This is how I complete all my own development work to avoid things like this. In my view there is to much happening within this formula and it can become very hard to audit as you are seeing.
Another way to simplify things is using variables. Also highly recommend this as a formula strategy as well (but it still doesn’t solve the auditing problem in a lot of cases)
https://blog.enterprisedna.co/2017/10/11/using-variables-in-dax-a-detailed-example/
Just quickly glancing at the formula, the second part of the DIVIDE function, is really not acheiving anything past [Total Due Debt] from what I can see.
All you should need on the denominator is the measure [Total Due Debt]
Also check out the techniques used here for how to complete this better using TOPN
There’s an error in this video but it’s explained in the comments section (only small adjustment required)
Hi Sam,
Thanks for your response, i have been looking through these but am trying to get the table to filter down to just the requested number of entries.
The top 5 debtors formula does this fine
Top 5 Debtors =
CALCULATE( [Total Due Debt],
filter(values(Contracts[Customer Name]),
if(rankx(all(Contracts[Customer Name]),[Total Due Debt],,DESC) <=5, [Total Due Debt],blank() )))
but getting the % of the total doesn’t work, i tried just the total sales as a division however the line by line % still don’t work. Areyou able to tell me what i need to do to get the line by line % to show as a % of the total sales like it does on the total line. Thanks
Top 5 Debtors =
CALCULATE( [Total Due Debt],
filter(values(Contracts[Customer Name]),
if(rankx(all(Contracts[Customer Name]),[Total Due Debt],,DESC) <=5, [Total Due Debt],blank() )))
Ah ok I think I understand slightly better now.
I was thinking you wanted to divide by all debt but it seems you only want to divide every row by the total of the top 5 customers
Try something like this.
This is all you need for the top 5 (you don’t really actually need the IF statement
Top 5 Customer Sales =
CALCULATE( [Total Sales],
FILTER( VALUES( Customer[Customer Name] ),
RANKX( ALL( Customer[Customer Name] ), [Total Sales], , DESC ) <= 5 ) )
Then to get the %
Move to this calc
Top 5 Sales % =
DIVIDE( [Top 5 Customer Sales],
CALCULATE( [Top 5 Customer Sales], ALL( Customer[Customer Name] ) ) , 0 )
See how you go with this.