Forum Discussion

am_i_really's avatar
am_i_really
Frequent Visitor
3 years ago
Solved

DAX Average

Hi All,

I'm working on a test project for school but I'm lost...  I was given a sample report and I have to create a matrix table like below:

This is the question:

We'd like to compare how durable the Director/Actor relationships are by continent, represented by Netflix releases. We'd like to measure this by average number of movies done together, average length of time between their first and last movie together, and average number of movies done together per year of director's longevity. Please put together a matrix visualization with the results of this analysis and please also try to complete this challenge without modifying the data model. That means no calculated columns, calculated tables, or query modifications

 

I've attached the file: https://1drv.ms/u/s!AnJmyZTL2iv4gZEhp2wzhSZzgZyrDw?e=YpEK8c 

Any help at all will be highly appreciated. Thank you in advance!

  • Greg_Deckler's avatar
    Greg_Deckler
    3 years ago

    am_i_really Try this as I believe this provides the results shown:

     

    AvgMoviesTogether 2 = 
    VAR __Table = SUMMARIZE(FILTER('Netflix',[director]<>BLANK() && [cast] <> BLANK() && [director]<>[cast]),[director],[cast],"__MoviesTogether", COUNTROWS(DISTINCT('Netflix'[show_id])))
    VAR __Result = AVERAGEX(__Table,[__MoviesTogether])
RETURN
    __Result

     

     

    Now, as to which is "correct", that's a bit of a different matter. The dataset seems kind of jank. For example, HARRY BRADBEER as director did only one show, s2010 but for some reason the Netflix table lists it basically three times, once for Dramas, Children & Family Movies and Action & Adventure. Seems like kind of a hacky way to put the same thing in three categories I guess. So, the GROUPBY method counts this as 3 because it does not distinguish DISTINCT show_id while the second method using SUMMARIZE counts this "correctly" as 1 since it accounts for DISTINCT show_id.

7 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    3 years ago

    am_i_really Here is the first one. The others should be a fairly similar pattern. Not sure if the screen shot is supposed to be the answers because I get different numbers. In the formula below I provide 2 options. __Table filters out the rows where the director is blank (null) which I feel would be the right way to do it. __Table1 does not include this filter. Currently, this formula returns results for __Table and not __Table1 but you can easily switch it.

     

    AvgMoviesTogether = 
    VAR __Table = GROUPBY(FILTER('Netflix',[director]<>BLANK()),[director],[cast],"__MoviesTogether", COUNTX(CURRENTGROUP(), [show_id]))
    VAR __Table1 = GROUPBY('Netflix',[cast],"__MoviesTogether", COUNTX(CURRENTGROUP(), [show_id]))
    VAR __Result = AVERAGEX(__Table,[__MoviesTogether])
RETURN
    __Result

    If your professor gives you low marks for not using CALCULATE, refer them to this:

     

    • am_i_really's avatar
      am_i_really
      Frequent Visitor
      3 years ago

      Greg_Deckler 

       

      Thank you for your answer but its not giving the result that he specified. The screenshot that was attached is what he said the results have to be. Any idea how we may be able to get that?

      • Greg_Deckler's avatar
        Greg_Deckler
        Community Champion
        3 years ago

        am_i_really Try this as I believe this provides the results shown:

         

        AvgMoviesTogether 2 = 
    VAR __Table = SUMMARIZE(FILTER('Netflix',[director]<>BLANK() && [cast] <> BLANK() && [director]<>[cast]),[director],[cast],"__MoviesTogether", COUNTROWS(DISTINCT('Netflix'[show_id])))
    VAR __Result = AVERAGEX(__Table,[__MoviesTogether])
RETURN
    __Result

         

         

        Now, as to which is "correct", that's a bit of a different matter. The dataset seems kind of jank. For example, HARRY BRADBEER as director did only one show, s2010 but for some reason the Netflix table lists it basically three times, once for Dramas, Children & Family Movies and Action & Adventure. Seems like kind of a hacky way to put the same thing in three categories I guess. So, the GROUPBY method counts this as 3 because it does not distinguish DISTINCT show_id while the second method using SUMMARIZE counts this "correctly" as 1 since it accounts for DISTINCT show_id.

  • sandy2112's avatar
    sandy2112
    Frequent Visitor
    3 years ago
    Average Movies Together =
    AVERAGEX(
        SUMMARIZE(
            FILTER(Netflix,Netflix[director]<>BLANK() || Netflix[cast]<>BLANK() && CONTAINSSTRING(Netflix[subcategory],"Movies")),
            Netflix[director],
            Netflix[cast],
            "No of Movies Together",DISTINCTCOUNT(Netflix[show_id])
        ),
       [No of Movies Together]
    )
    AverageReleaseLength =
        AVERAGEX(        
            SUMMARIZE(
              FILTER(Netflix,Netflix[director] <> BLANK() && Netflix[cast] <> BLANK() &&CONTAINSSTRING(Netflix[subcategory],"Movies")),
              Netflix[director],
              Netflix[cast],
              "Min of Release Year",MIN(Netflix[Year_added]),
              "Max of Release Year",MAX(Netflix[Year_added])
            ),
            [Max of Release Year]-[Min of Release Year]+1
        )
    ----
    Average Longevity is still showing some different values when we keep under continent
    Average Longevity =
    VAR T1 =
        SUMMARIZE (
            FILTER (
                'netflix',
                [director] <> BLANK ()
                    && [cast] <> BLANK ()
                    && [show_id] <> BLANK ()
                    && [director] <> [cast]
                    && RELATED ( 'Content'[category] ) = "Movie"
            ),
            'netflix'[director],
            'netflix'[cast],
            "Longevity",
                CALCULATE (
                    MAX ( 'Content'[release_year] ) - MIN ( 'Content'[release_year] ) + 1,
                    ALLEXCEPT ( Netflix, 'Netflix'[director] ),
                    CROSSFILTER ( 'Content'[show_id], Netflix[show_id], BOTH )
                ),
            "# of movies", COUNT(Netflix[cast])
        )
    RETURN
        DIVIDE ( SUMX ( T1, [# of movies] ), SUMX ( T1, [Longevity] ) )