Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
IPGeorgiev
Helper III
Helper III

Bucket via measure to column chart

Hi all,

 

I have a CSAT% which is calculated as follows = postive reponses/positive+negative.


Then for each employee we have a specific CSAT% and I wanted to do a bucketing with this:

 

IPGeorgiev_0-1616622335225.png

Now this works perfectly if I use it in a matrix. And I need to do it like this, as a calculated column wont do the job as CSAT% needs to be calculated for the selected data range and for each date there are multiple rows for positive and negative responses data.

 

So what I want to achieve at the end is this:

 

IPGeorgiev_1-1616622432455.png

 

Soo this basically shows the count of employees belonging to the respective bucket for the given month.

 

However I am unable to put achieve this as I am stucked at the point where I select Stacked column chart and as AXIS i put TimeMonth and as Values Employees (Count of employees) and now all I need is to put the above Bucket measure as a Legend and it will give me exactly the result I want, however it doesnt work ( doesnt let me put it in the legend field)

 

Any ideas how this can be mitigated?

 

Many, many thanks in advance!

 

Best regards,
Ivan

17 REPLIES 17
v-cazheng-msft
Community Support
Community Support

Hi @IPGeorgiev 

You can try the following steps.

 

1 Create a Calculated column

Category =

var res=[CSAT%]

return IF ( res > 0.89, ">89%",IF( res < 0.8,"<80%",IF( res > 0.7999 && res < 0.8762, "80%-87.61%", "87.61% - 89%")))

 

2 Create a Clustered column chart visual

v-cazheng-msft_0-1617081901803.png

 

It will look like this:

v-cazheng-msft_1-1617081901806.png

 

 

Best Regards

Caiyun Zheng

 

Is that the answer you're looking for? If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

 

IPGeorgiev
Helper III
Helper III

Hi @Ashish_Mathur ,

 

here you can download it - CLICK HERE

 

I have added a dummy data in this file as the other is confidential. I have created the CSAT% measure and the Bucket measure and created the visual, however I cant put Bucket into Legend.

 

Thanks a lot in advance!

 

BEst regards,
Ivan

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

@Ashish_Mathur you are a genius!! Worked perfectly!!

 

May I ask how we can make one more adjustment -

 

If we have two buckets Above the target and below the target and the target changes each week - how can we make it to be variable depending on the week

 

so for exmaple

 

for Week 11 - the target is 89% so 1 bucket is everyone above 89 and the other one everyone below 89 - then for Week12 the target is 87% and one bucket is everyone above 87% and the other everyone below 87%

 

Any ideas about this?

 

Many, many thanks one more time for the efforts and the time invested in helping me - it really means a lot!

You are welcome.  Thank you for your kind words.  I am not clear about your new requirement.  Show me the exact result you want to see.

Also, if my previous reply helped, please mark that as Answer.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hi @Ashish_Mathur ,

 

many thanks for the response!

Here an updated pbix file. - https://drive.google.com/file/d/1LzEwlt7jEhGeZtZqsjlvpE-cKGj5-u5M/view?usp=sharing 


To the CSAT table I have added a new column called channel(communication channel). I have also added a new table called targets.

What I want is to have the same visual  - instead of Week we should use TIme Month and the columns should show two groups and the respective amount of agents belonging to each group.

 

Those above the target and those below the target. Meaning that each bucket will calculated  the count of employees:

with CSAT for the respective MONTH which are above the TARGET for the selected CHANNEL and MONTH - meaning that it should find the correct target value from the target table based on Channel and Month .

 

Hope this is clearer now.

 

Many, many thanks in advance!!

 

Hi,

I still do not understand.  Based on the data that you have shared, show the exact result you are expecting in a simple Table.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

HI @Ashish_Mathur , apologize for not making this clear.

 

I have prepared an exel file which shows the output I am looking for - I have made some tweaks to the data (sheet test) where I have added Channel and Month is not not just Feb but also March. 

 

In the output sheet you can see what I am aiming for - "desired final ouput"

 

Here a link - https://drive.google.com/file/d/1F-0VTpOGwEokUKFf9HQ7caCzNdvZW2CV/view?usp=sharing

 

Many thanks in advance!

 

Best regards,
Ivan

Hi,

In the Target worksheet, what is the usage of Target 1 and Overall 1?  Which one is relevant for our calculations?

 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

@Ashish_Mathur sorry for the confusion! Whats needed here is Target1 we can disregard Overall1 for this one.

Hi,

Based on the workbook that you shared with me, is this the result you are expecting?

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hi @Ashish_Mathur  - yes!! This is it!

Hi,

I'd like to test my solution for speed of execution.  To do so, I will need the actual number of rows in the Test and target worksheets of the Excel workbook that you shared with me.  For reasons of confidentiality, you may anonymise the data in the columns of both worksheets, if you so wish.  I am not concerned with what is under the column.  I am only concerned with whether my solution can handle the extensive data that you may have in both worksheets. 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hi @Ashish_Mathur ,

well its quite big I think - I mean its daily data and its being stored for 1 year so there a multiple files and the total number of rows is around 1mil. 

 

I can get a file a few days but with the all data it will be quite hard to have it.

 

Should I upload a file for a week for example ?

 

THanks!

 

Best regards,
Ivan

Share as much data as you can.  My intention, as stated in the previous message, is to see whether my solution will work on a large dataset or not.  The more the data you share, the better i will know whether my solution works or not.  There is no point in sharing my solution with you if it does not work on your actual data size.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hi @Ashish_Mathur  sorry for the delay! I will work over the weekend and try to provide you a data with more rows to test. Thanks! If you share the method I could also test it with the bigger dataset 

Ashish_Mathur
Super User
Super User

Hi,

Share the link from where i can download your PBI file.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.