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 d...
  • 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.

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] ) )