aggregating expected time (duration) of multiple independent trainings

[Jnkrick]

I'm trying to compare the actual time it took someone to do a training compared to the expected time. For this expected box, I've put a temporary solution that is only correct if you select ONE class but not multiple (min formula)

I'd like someone to please help me with a calculated measure that would find the expected duration column and add together the amounts of expected time based on the filter selection. 

So there are 13 courses:

course A  15 mins

course B  15 mins

course C  15 mins

course D  15 mins

course E  15 mins

course F   67 mins

course G  35 mins

course H  95 mins

course I   102 mins

course J   35 mins

course K  94 mins

course L  93 mins

course M 62 mins

I'd like the card to add all of the  expected mins together since ALL of the courses are currently selected. So the card should say 659 mins but when I filter to have say course A and course B the card should say 30 mins

Thank you!!

[Jnkrick]

Gotcha! 

Thank you!! So the slicer I put on my main page, how would I get it to work for both the fact and dim... say I wanted to see only a certain course in the slicer?

[Whitewater100]

Hi:

So each dimension Table (Dates, Products, etc) would only have unique entries and each of these dim tables should connect to your fact table(s). The fact table can and should have many entries on any given item e.g., a product can be repeated lots of times in the fact table but only appears once in a dimension table.

When do any visuals always use the columns/fields from the dimension tables.

Does that make sense? Thanks..

[Jnkrick]

Ok so I got the dim table to work with the slicer , the problem I'm having now, is that the other cards I have here need to be off the fact table as they are the median of all completion times in fact table. 

So when I replaced the TITLE slicer to be connected to the dim table, it messed everything else up.

[Whitewater100]

Hi:

If you want the cards to ignore slicer you can alter the measures slightly.

For example. 

# Courses = DISTINCTCOUNT(Table[Course_ID])

New # Courses

                   = CALCULATE([# Courses]), REMOVEFILTERS())

Does this help? Thanks..

[Jnkrick]

I need the filter in the slicer to work for both the fact table and the dim table. The are connected here by title and by course LOID(unique identifier).... Do the Title columns in each of these have to share the same name?

So in the picture above, I changed the slicer to use the dim table title and it broke all the cards. I would like it to work like this. I hit the title slicer for whichever course I want and the expected time uses the dim table to get 30 but then the other cards use the fact tables to get the stats on users of those courses, like median minutes it took.

[Whitewater100]

Hi:

Here is a file I think we were using. The cards appear to work, just a few examples..

https://drive.google.com/file/d/1q3kCWzWV5jy-cgqnrf7GivcHl8kIjUjv/view?usp=sharing 

[Whitewater100]

Hi:

Sorry for any misunderstanding. Do you have a file(example) you can share?

There should only be one active relationship. What is going on is (I think) you have multiple dimensions across tables serving as both fact and dim tables. 

The Fact Table should be ometihng like:

CourseID

StudentID

LocationID

Date signed up

Date graduated

etc facts

Then you have dim tables for students,courses,locations. Each dim table has only unique records in them. The fact table can have multiple repeating dimesions. 

It will be eassier to describe with actual data. I think you are close..

[Whitewater100]

Hi:

Please see attached. I hope this helps!https://drive.google.com/file/d/1q3kCWzWV5jy-cgqnrf7GivcHl8kIjUjv/view?usp=sharing 

[Jnkrick]

For further complication.... there are prob 50,000 completions of each of these courses. How do I make sure that it only grabs the one unique value and then adds to additional unique expected time selected in the filter?

[Whitewater100 · Accepted solution]

Hi:

The dimension table is unique and needs to be so. That's the figures you are seeing in your slicer. If you have 50,000 factual details about whose taking which courses, those belong in a fact table which will have a relationship to the Course dim table. The Course dim table can have other descriptive info about the courses, like level, location, etc. Most models try to follow a Star-Schema approach which allow for much deeper analysis.

If you have some new scenarios with actual test data, I'm happy to try to answer other questions!

I hope I've answered your question.(s). Thanks..