Odd Percentage Calculation

[dom-mitchell]

Hi everyone, I'm sure this is really basic but for some reason, I just can seem to get my head around what I need to do!

I'm building a report that shows the training records of individual employees; this is controlled by a slicer to choose the employee. 

The data comes from two tables:

1. A SharePoint list that is updated via Power Automate when someone books on to the various sessions - Training Bookings

2. A SharePoint hosted Excel Workbook that contains the additional detail for each Session - MasterSessionList

Training Bookings has two key columns: WorkshopTitle

MasterSessionList has two key columns: Theme and Title

I have created a relationship between WorkShopTitle and Title which is working. 

From those I have created a series of New Measures: 

Candidate Count: 

Candidate Count = COUNTROWS(FILTER(MasterSessionList,MasterSessionList[Theme]="Candidate"))

WorkshopTitle Count: 

WorkshopTitle Count = COUNT('Training Bookings'[WorkshopTitle])

%CandidateSessions:

%CandidateSessions = DIVIDE('Training Bookings'[WorkshopTitle Count],'MasterSessionList'[Candidate Count],0)

I have also set this number as a percentage data type.

Using some dummy data, the user I am focusing on displays the following:

WorkshopTitle Count = 2 (this is correct; they have attended two workshops where the theme is Candidate)

CandidateCount = 3 (this is correct; there are three workshops that are of the Candidate theme)

%CandidateSessions = 233% (obviously wrong; 2/3 should = 66%)

Using a different user, I get results that appear accurate:

WorkshopTitle Count = 1 (this is correct; they have attended one workshop where the theme is Candidate)

CandidateCount = 3 (this is correct; there are three workshops that are of the Candidate theme)

%CandidateSessions = 33% (correct, they have attended one third of the Candidate themed workshops)

I'm completely stumped as to how this is happening. I'm hoping a different set of eyes is able to unpick what is happening here. 

[dom-mitchell]

Not entirely sure what was going wrong but with no further changes, it just started working as expected. Thanks for the help all!

[v-zhangti]

Hi, @dom-mitchell 

Can you provide some sample data or simple pbix files? Sensitive information can be removed in advance. What kind of expected results do you expect? You can also show it with pictures.

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@dom-mitchell , can you check the the calculation by having all three measures in a visual ( all of them should be measures)

I doubt that can happen, unless there some data issue

If this does not help
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.