Show percent of column total on a line graph, all and removefilters not working

[Singularity9]

I have a table, Table 1, which includes data on sales to different customers. I want to show the % of sales to each customer, split by date. Eg, in January we sold 100 units, 30 to customer 1, 50 to customer 2, and 20 to customer 3. Then in February we sold 200 units, 40 to customer 1, 130 to customer 2, and 30 to customer 3. I want the graph to show percentage for each month, something like this:

SaleDate:	Jan	Feb
Customer 1	30%	20%
Customer 2	50%	65%
Customer 3	20%	15%

It's easy to do on a table as I can just select show value as percent of column total. But when i try to display it on a line graph the measure goes completely insane. I have tried:

% of Total = var _1 =

CALCULATE(

Distinctcount(Sales[SaleID]),

ALL(Sales[Customer]))

return divide(Distinctcount(Sales[SaleID]), _1)

I have also tried

% of Total =
var _1 =

CALCULATE(

Distinctcount(Sales[SaleID]),

REMOVEFILTERS(Sales[Customer]))

return divide(Distinctcount(Sales[SaleID]), _1)

Both of them return values of 100% for all customers.

I have also tried:

% of Total = var _1 =

CALCULATE(

Distinctcount(Sales[SaleID]),

allexcept(Sales, Sales[Customer], Sales[SaleDate]))

return divide(Distinctcount(Sales[SaleID]), _1)

Which just gives me something which appears to be a percentage of grand total, so I don't get it split by month - in the example above, Customer 1 in January would be 30/(Jan+Feb), or 30/300, or 10%. What am I doing wrong and why do these functions have such a problem?

Note: I will need to be able to filter the visual by other slicers on the Sales table.

[Anonymous]

Hi, @Singularity9 

Let's say your source data looks like this:

Measure = 
Var _all=CALCULATE(SUM('Table'[Sales]),ALLEXCEPT('Table','Table'[SalesDate]))
Var _every=SUM('Table'[Sales])
RETURN
DIVIDE(_every,_all)

Is this the result you expected? If not, please provide more information about the source data.

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Singularity9 · Accepted solution]

That had the same effect, but the visual did help me figure out the problem: it's not working when I have sale date as a hierarchy. When I created a new calculated column which displayed the sale month and year instead, that worked and each column is now summing to 100%. Thanks for your help.

[bhanu_gautam]

@Singularity9 , Try using

% of Total Sales =
VAR TotalSalesPerMonth =
CALCULATE(
SUM(Sales[SaleID]),
ALLEXCEPT(Sales, Sales[Date])
)
RETURN
DIVIDE(
SUM(Sales[SaleID]),
TotalSalesPerMonth,
0
)

[Singularity9]

Thanks, but this is basically the same as my third query, and has the same result - sales per month are shown as a percentage of all sales for the entire time period, not just all sales for that month.