Count the dynamic value with slicer for further calculation

[Anonymous]

As a new user of Power BI, I have face a problem from a complicated filtering problem. Here is the situation and background. I am now using a data for a survey response, and some question consist of multiple selection.

As I need to separate the multiple choice and unpivot it, I have created the “Q1”. It is a duplicate from “Sheet1” and further process it to contain only 1 option in each row.

As I want to have a data of percentage of each choice among all the people (base value is the total of customer, instead of total number of options selected), I have created a calculated table and calculated column to summarize.

However, the problem is, I could not find a method to apply the same logic to fit in the slicer for column “Unit”. For example, when I click the slice for “UnitA”, the percentage showed should be each options total divided by “UnitA” customer. It could be a separate calculation and visual for me, as I will put it in a new page. May I ask how can I solve this problem? Thanks a lot!.

https://1drv.ms/u/s!ArsiYEZYDDWRgm-hDh4opuKb4bQi?e=MXJX6T

[CheenuSing]

Hi @Anonymous ,

I went through the pbix file. To acheive the results to show properly when sliced Unit do the following.

1. Instead of displaying Sum of q1_percent make it show the values as Percentage of grand

2. Right Click on the sum of Q1_percent in the value column in the visualisation pane

   

And that is it.

After doing this when you slice on Unit B the result should be

If you wanted a different result please share more details

Cheers

CheenuSing

[Anonymous]

Thanks for your passionate help first! @CheenuSing 

However, there is some misunderstnading for the logic of chart. In order for better explanation, I have re-upload a file again and changed some data in here.

https://1drv.ms/u/s!ArsiYEZYDDWRgnPYRqeNb2o6cJ2X?e=YdEVsx

In the excel file, I have insert 11 more uncollected response to reflect my actual situation right now. Thus, I will use column "collected" to count the total responded customer.

In the updated power BI file, you could find 2 bar chart. The left one is following your method, and the right one is bar chart I want to achieve at the end. After I manipualate the data, you can see the difference. The differentcce between 2 chart is that, the base to get the percentage is different. I want to reflect the percentage of "respondent" choose that option, instead of the percentage of the options among the customer "respond".  

But the problem for me now is that, because I get the percentage by directly divide by a fixed value, therefore I would like to ask for advice. At the end, I want to achieve to create a chart of showing how many customer choose that options, while I could use filter. Then the filter can help me change the base value for division. Thanks again @CheenuSing