cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Jnkrick
Frequent Visitor

Need to visualize percent above and below expected time for trainings

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?

Jnkrick_0-1652203447314.png

 

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. 

Jnkrick_1-1652203514255.png

 

HELP! Thank you!!

6 REPLIES 6
Jnkrick
Frequent Visitor

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

Jnkrick_0-1652994567497.png

 

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

Jnkrick_1-1652994612745.png

 

connected dim and fact tables by values

Jnkrick_2-1652994889035.png

 

Tried to throw it into a bar chart and nothing?

Jnkrick_4-1652995208499.png

 

 

 

 

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

 

tctrout_0-1653053360978.pngtctrout_1-1653053399144.png

 

Icey
Community Support
Community Support

Hi @Jnkrick ,

 

Can what @tctrout mentioned solve your problem?

 

 

Best Regards,

Icey

tctrout
Resolver III
Resolver III

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.  


tctrout_0-1652207092026.png

 



Jnkrick
Frequent Visitor

Sorry, getting to it now. 

So I would relate to my fact table with value?

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.

Helpful resources

Announcements
August 2022 update 768x460.jpg

Check it Out!

Click here to learn more about the August 2022 updates!

August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors