Percentage from column in clustered chart

[Anonymous]

Hi there,

I need to have a clustered chart that displays 100% of the values per country.
Currently 100% is being distributed by all countries across all buckets ( Ihave selected "Percentage from Total")

i.e. In the below screenshot I would like to see a country like UK to have 20% on each bucket (1,2,3,4,5) to complete 100% per country.

Does someone know how to fix this?

Thanks

[Arentir]

Hi @Anonymous, can you share the file with us?

[Anonymous]

Hi @Arentir,

Here is the pbix.

Thanks

[Arentir]

Hi @Anonymous,

Can you tell me what is the calculation about? The count of row in table by bucket? If so for UK you would like to achieve this result in the graph?

[Anonymous]

Hi @Arentir,

Here is the new pbix, hope you can help!

Thanks

[Anonymous]

Any thoughts here please?

[Anonymous]

Hi @Arentir

Sorry, that was actually only a sample but I think I forgot to give some context.

Let's say that the bucket is distribution placements.

i.e:

Bucket 1: case responded within 1hr
Bucket 2: case responded over 1hr and below 2hr
Bucket 3: case responded Over 2 hr and below 3hr
and so on..

See here an example of the data I have:
Month | Name | Country | Bucket | Avg Time of response ( I have all of these variables and more, but I think these are the only ones relevant)
March - Jake - UK - 4 - 4.27
March - Lori - USA - 2 - 2.10
March - Stephen - UK - 2 - 2.20
April - Jake - UK - 3 - 3.39
April - Lori - USA - 3 - 4.8

Here, this is the output I am looking for:
(Filter by March)
UK - Bucket 4 - 50%
UK - Bucket 2 - 50%
Totaling 100% per country in March
--If I were to click on Bucket 4, then I would see on a syncronized table with the "Name" field and "Avg Time of Response" as "Jake | 4.27".

This is what I have right now:
REP%Distinc = COUNTA('SolvedCasesData (3)'[Bucket])/CALCULATE(COUNTA('SolvedCasesData (3)'[Bucket]),ALLNOBLANKROW('SolvedCasesData (3)'[Bucket]))

I also want to be able to manipulate the data by adding/removing more months or filtering by certain countries.
At the moment I am seeing the same people showing on different buckets on the same chart when I select multiple months and they show different hours.

I am guessing that somehow I am not calculating the average of the ''names'' when selecting multiple ''months''.
So if I select March and April, Lori will have 2 different buckets. But What I really need is to calculate the average of their response time and then assign to a bucket.

I have this formula in an excel to calculate the buckets, maybe this is the thing causing issues and it should be on power bi for dynamic calculation?

=IF(J11<3.59,1,
IF(AND(J11>=3.6,J11<=4.49),2,
IF(AND(J11>=4.5,J11<=5.39),3,
IF(AND(J11>=5.4,J11<=6.29),4,
IF(J11>=6.3,5,
)))))

Basically what the above does is to check the J column that has the "Avg time of response" and then puts each line into a bucket on a different row. I have imported the excel from power bi and this is the data I was working on.

Any help would be much appreciated!

Thanks