Filtering the Legend of a Pie Chart

[Seanan]

Hi guys,

I am currently trying to filter the values displayed in the legend of a pie chart based on two conditions and seem to be running into an issue.

I want the pie chart values in the legend to be dynamic as the values may change which means that the legend will need to adjust to the changes in the values. Currently the legend should only be displaying what values are being shown in the pie chart and not a big list of all the different "QuestionNo's". The code I have is as follow's, along with a screenshot of the error message I am getting:

Section1FilteredQuestions = FILTER(GroupedQuestions,LEFT(GroupedQuestions[QuestionNo],2) = "1" && GroupedQuestions[RequirementResults] = "Does Not Meet Requirement")

I hope I have explained what I am looking for well enough and would appreciate any help.

[Pragati11]

HI @Seanan ,

Can you share some screenshots around current pie-chart visual and expected pie-chart visual?

Share some sample data as well.

Is your calculation above column or measure?

Thanks,

Pragati

[Seanan]

Hi Pragati,

Thanks for the reply and I apologise for the delayed response.

The current pie chart looks like:

The expect pie chart should look like(I set the advanced filter to show the desired outcome): 

The pie chart should be able to adapt to changes in the QuestionNo values. For example today it may be 1.3, but in 2 weeks it could be 1.3 and 1.6. Therefore, it will need to be able to pickup these changes and add or remove QuestionNo's from the legend.

Sample data is as follows: 

The formula mentioned above is a column.

[Pragati11]

HI @Seanan ,

Is Question No (which is a legend) and the metric on pie-chart coming from same table?

Can you share a full screesnhot of pie-chart visual showig what all columns are moved to this visual?

Basically I want chart along with what is moved to the chart under Visualisation pane and Fileter Pane as shown below:

Ideally the pie-chart should dynamically react to any filters selected.

Thanks,

Pragati

[Seanan]

Hi Pragati,

Yes the QuestionNo and metric on the pie chart come from the same table.

Currently I use the advanced filtering to narrow it down, but hopefully we can move away from that and come up with a solution to only show the values within the pie itself.

All of the data shown comes from the same table.

[Pragati11]

Hi @Seanan ,

I am still not very sure on the issue here. On what basis do you just get One value on your left pie-chart for just Qiestion: 1.3? I don't see any slicers or filters on the report. I am not able to understand this part here.

Is it possible to share the pbix file by removing any sensitive content?

You can upload it to dropbox or WeTransfer and then share the link here.

Thanks,

Pragati

[Seanan]

Hi Pragati,

Unfortunately I am unable to share the pbix file as it has permissions preventing you to acces it and I am unable to grant access for you.

I will try to explain everything as best as possible to make things clear. Essentially the PowerBI report is connected to a SQL Database. This database then feeds Questionnaires data to the PowerBI Report. Currently there are 120 questionnaires in the production database (I am using a test database). The pie chart will capture all of Section 1's questions and answers, that "do not meet the requirement". For example imagine out of all the 120 questionnaires, 50 of them had QuestionNo 1.3 set to "Does not meet requirement" and another 50 questionnaires had QuestionNo 1.6 set to "Does not meet requirement". The pie chart should now be showing 50% 1.3 and 50% 1.6. Now let's jump a month in the future and pretend we now had 150 Questionnaires in the database and now there is another 10 that has QuestionNo 1.2 set to "Does not meet requirement". The Pie Chart should now be showing 45% 1.3, 45% 1.5 and 10% 1.2, meaning that the Legend of the pie chart will now need to adjust to accomidate for QuestionNo 1.2 being added. Meaning that instead of the legend just saying 1.3 and 1.6 it should now also include 1.2. 

Each month the QuestionNo's in the legend may change based on the top 5 QuestionNo's that have the highest record of "Does not meet requirements", when looking at all of the questionnaires answers combined.

For Section 1 there is 14 questions but in the pie chart I only want to see the top 5 results that meet the specified criteria. I have worked out the code for narrowing down the top 5 results, labelled "Section 1 Top 5" in the values for the pie chart. I just need to work out how to also allow the legend to dynamically adjust to the changing values.

I am sorry if it is not very clear, as my explaining of things is not the greatest.  

[Pragati11]

HI @Seanan ,

Thanks for the explanation.

Can you also tell what is the calculation behind  "Section 1 Top 5"? If you have calculated it within Power BI using DAX or in Power Query, then can you share the formula for this calculation?

Thanks,

Pragati

[Seanan]

The formula is a DAX Expression measure:

Section 1 Top 5 = VAR Sec1Top5 = RANKX(FILTER(StagingTable,LEFT(StagingTable[QuestionNo],2) = "1."), [Section 1 Does Not Meet Requirements],,DESC) RETURN IF(Sec1Top5 <= 5, [Section 1 Does Not Meet Requirements], BLANK())

Section 1 Does Not Meet Requirements is another measure:

Section 1 Does Not Meet Requirements = IF(ISBLANK(CALCULATE(COUNT(StagingTable[QuestionNo]),FILTER(StagingTable,AND(LEFT(StagingTable[QuestionNo],2) = "1.", StagingTable[RequirementResults] = "Does Not Meet Requirement")))),0,CALCULATE(COUNT(StagingTable[QuestionNo]),FILTER(StagingTable,AND(LEFT(StagingTable[QuestionNo],2) = "1.", StagingTable[RequirementResults] = "Does Not Meet Requirement"))))