How to Adjust the Percentage in a Bar Chart

[Anonymous]

Hello, 

I have a data set and I've simplified a sample data sheet like this: 

Date	Customer	Volume
Sep-19	A	18
Sep-19	B	15
Sep-19	C	28
Oct-19	A	20
Oct-19	B	10
Oct-19	C	30
Nov-19	A	25
Nov-19	B	8
Nov-19	C	20
Dec-19	A	30
Dec-19	B	15
Dec-19	C	15

It’s easy to get percentage data by month (in this case: percentage by column) in Matrix. See below:

I also want to create a bar chart with a trend line by month using the percentage numbers. When I chose the “show value by percentage” in the bar chart, I only saw “by grand total” option. So instead calculating the percentage by month, it’s calculating by customer, see below. You can see the percentage number of A is 19.35% not 29.51% in the Matrix. I don't want that. 

Can someone help me out here? I’ve been stuck here for quite a while. Tried a few measures, it didn’t work.

Thank you in advance!!!!

[v-alq-msft · Accepted solution]

Hi, @Anonymous 

Based on your description, I created data to reproduce your scenario.

Table:

You may create a measure and then go to 'Modeling' ribbon, format it as follows.

Percentage of column value = 
DIVIDE(
    SUM('Table'[Volume]),
    CALCULATE(
        SUM('Table'[Volume]),
        ALLEXCEPT('Table','Table'[Date])
    )
)

 

Result:

Best Regards

Allan

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

[amitchandak]

This how you can try. But it will work when you separate out the customer in a new table

option1 = sum(table[Volume])/calculate(table[Volume],all(table[Customer]))
//Customer in new table
option2 = sum(table[Volume])/calculate(table[Volume],all(Customer))

[Anonymous]

@Anonymous Try upivoting Customer & Volume columns in 'Transform Data' section &

Then use the following dax & have it in value of the chart: I am assuming the volume column is count of a key column

Monthly % =
var p = CALCULATE(DISTINCTCOUNT(Table[contact_key]),Table[Value]="A")
var l = CALCULATE(DISTINCTCOUNT(Table[contact_key]),Table[Value]="B")
var m = CALCULATE(DISTINCTCOUNT(Table[contact_key]),Table[Value]="C")
var s = p+l+m
return CALCULATE(DISTINCTCOUNT(Table[contact_key]),FILTER(Table,Table[Value]=MAX(Table[Value]) && Table[Month]=MAX(IRS_Sessions1[Month])),FILTEr(Table,Table[Value] in {"A","B","C"}))/ CALCULATE(s,filter(all(Table),Table[Month]=MAX(Table[Month])))