Forum Discussion

dom-mitchell's avatar
dom-mitchell
Frequent Visitor
4 years ago

Odd Percentage Calculation

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. 
 

 

Need Help

3 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    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.

  • v-zhangti's avatar
    v-zhangti
    Community Support
    4 years ago

    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.

  • dom-mitchell's avatar
    dom-mitchell
    Frequent Visitor
    4 years ago

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