Need to visualize percent above and below expected time for trainings

[Jnkrick]

I would like to be able to visualize (in a bar chart most likely) a count of how many people sit within certain percentages above or below the expected time of a training. 

So there is an expected time of a training which doesn't change. If course A has an expected time of 15 mins but learner completed course A in 30 mins then obviously they completed it in twice the expected time. 

I tried using percentile calculations but it doesn't make sense when visualized. Maybe it needs to count how many are in that category for me to be able to put it in a bar chart of how many learners were in this bucket?

I tried the quartile formula on this forum too and it's not visualizing either.. but maybe the same problem? I need learners bucketed into these categories and then counted so that I can see how many learners are in each bucket. 

HELP! Thank you!!

[Jnkrick]

Ok in dim table I've put a category to each percentage point

I've got a variance value for each line in the fact table (student records) they range from -1 to 1.25 so I put some cushion in the dim to 1.5

connected dim and fact tables by values

Tried to throw it into a bar chart and nothing?

[tctrout]

Glad to help.

I mocked this up and was able to get results returned.  I suggest the following:

1. Be sure the data types between the 2 related columns are identical in both back end Transform Data and Front End column Tools. 
   1. I see that you created the variance via calculated column on the front end, this should work but my preference would be to do this in the backend transform data.  
   2. In my mock up, I initially had the column types as % in the backend Transform data and that provided 0 results, telling me my relationship wasnt working.  I went back and altered my data types to be fixed decimal and this fixed the relationships instantly.  I would have expected % to have worked since they were both set to %, but I suppose some kind of rounding was preventing it from finding a match in the 1:many relationship.
2. I see a page level filter in your image, ensure this isnt filtering out all your results.
3. I would create an explicit measure to do the counts by leveraging countrows(fact table)
   1. additional explicit measures such as average could be insightful as well

[Icey]

Hi @Jnkrick ,

Can what @tctrout mentioned solve your problem?

Best Regards,

Icey

[tctrout]

I am not familiar with those statistical functions, but I would solve this problem with basic math and star schema modeling.

The below digram outlines my approach.  Create a Fact table that contains a column computing the variance between Expected to Actual completion times to obtain a %.  

Then create a Dimension table that contains all possible combination and group them in your desired buckets.  The example does not contain all possible values, your dimension should be built from 1000% to -1000% in 1% increments (or a larger scale if desired).  

Create a releationship from your dimension to your fact tables by the % value.  Once complete you can simply count how many users fit within the buckets you custom defined.  

[Jnkrick]

Sorry, getting to it now. 

So I would relate to my fact table with value?

[tctrout]

Yes, once you build out your dimension that includes a row for every possible value within your range.  Just relate your dimension to your fact on the % (most likely still in decimal form) and youll be able to customize your groupings like above.