Percent of total client sales

[Gus_C]

Hi All,

I am trying to get a percentage of total value of sales per client for my line chart.

However, the percentage of total in the field option is giving me the percentage out of the filtered top N clients. But I want to get percentage out of all clients in that particular time period (sliced by month and year). (i.e I do not want topN to affect percentage).

My bar and line chart should show top 10 clients sales values (bar) and their percentage of sales (line) out of all clients in that sliced date. But im getting percentage of sales out of top 10 clients. 

I have tried:

=divide([my sales measure],calculate([my salesmeasure],all(table[client group])))

but to no avail. I either get 100% for all clients or the same as Grand total.

p.s I cannot find percent of column total (as some other solutions have suggested, not sure if thats whats needed anyway)

Thanks a lot for the help!

[ebeery]

@Gus_C  is [client group] the column you're applying the top N filter to?

If not, that would explain why the ALL() function is not performing as desired.

Do you have a client table?  Can you just filter ALL(clienttable) in your CALCULATE statement instead of the specific column?

Without seeing your data model or data structure it's hard to give any more specific advice.

[Gus_C]

Thanks for your reply @ebeery ,

Yeah I am applying top N filter to [client group] by value of their sales ([sales measure]), cant share pbix due to confidentiality unfortunately.

Is there a way to prevent topN filter affecting this percentage measure? that way it would take whole column rather than top N clients?

[ebeery · Accepted solution]

@Gus_C that's what the "all" function should be doing, so without seeing more details I can't speculate as to why that's not working.

I've tested the below measure in a similar scenario, with test data.  It seems to perform as expected.

% of Total Sales = 
VAR _Sales = [Sales]
VAR _TotalSales = CALCULATE([Sales],ALL(financials[Country]))
VAR _Result = DIVIDE(_Sales, _TotalSales)
Return
_Result