Forum Discussion
Percentage from column in clustered chart
Hi Anonymous, can you share the file with us?
- Arentir7 years ago
Resolver III
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?
- Anonymous7 years agoNot applicable
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.8Here, 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