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.

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
Icon for Community Champion rankCommunity 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.

  • am_i_really's avatar
    am_i_really
    Frequent Visitor
    3 years ago

    Thank you so much for your help and the explanation, the solution worked! If you don't mind can you please help with the other 2 columns as well?

    • Greg_Deckler's avatar
      Greg_Deckler
      Icon for Community Champion rankCommunity Champion
      3 years ago

      am_i_really I'm not sure how the other two columns are being calculated in terms of how those numbers were arrived at. For example, if I use what I think is logical I get different results. PBIX is attached beneath signature.

      AvgRelLength = 
    VAR __Table = 
        SUMMARIZE(
            FILTER('Netflix',[director]<>BLANK() && [cast] <> BLANK() && [director]<>[cast]),
            [director],[cast],[show_id],
            "__ReleaseYear", MAXX(FILTER('Content',[show_id] = EARLIER([show_id])),[release_year])
        )
    VAR __Table1 = 
        ADDCOLUMNS(
            SUMMARIZE(
                __Table,
                [director],[cast],
                "__MinYear",MINX(FILTER(__Table,[director]=EARLIER([director]) && [cast] = EARLIER([cast])), [__ReleaseYear]),
                "__MaxYear",MAXX(FILTER(__Table,[director]=EARLIER([director]) && [cast] = EARLIER([cast])), [__ReleaseYear])
            ),
            "__Diff", [__MaxYear] - [__MinYear]
        )
    VAR __Result = AVERAGEX(FILTER(__Table1,[__Diff]>0),[__Diff])
RETURN
    __Result

      and

      AvgMoviesLong = 
    VAR __Table = 
        SUMMARIZE(
            FILTER('Netflix',[director]<>BLANK() && [cast] <> BLANK() && [director]<>[cast]),
            [director],[cast],[show_id],
            "__ReleaseYear", MAXX(FILTER('Content',[show_id] = EARLIER([show_id])),[release_year])
        )
    VAR __Table1 = 
        ADDCOLUMNS(
            ADDCOLUMNS(
                SUMMARIZE(
                    __Table,
                    [director],[cast],
                    "__MinYear",MINX(FILTER(__Table,[director]=EARLIER([director])), [__ReleaseYear]),
                    "__MaxYear",MAXX(FILTER(__Table,[director]=EARLIER([director])), [__ReleaseYear]),
                    "__Count",COUNTROWS(FILTER(__Table,[director] = EARLIER([director]) && [cast] = EARLIER([cast])))
                ),
                "__Diff", [__MaxYear] - [__MinYear]
            ),
            "__NumPerYear", DIVIDE([__Count],[__Diff],0)
        )
    VAR __Result = AVERAGEX(FILTER(__Table1,[__Diff]>0),[__NumPerYear])
RETURN
    __Result