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.

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.

      • 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?

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Did you find any solution for the other 2 columns?