Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

How to group a group

I am trying to solve the following task.

 

I have a dataset of video plays for a certain period of time. I have to calculate the number of plays each video has. After that, I have to create a visual that would represent % of videos that the following groups of plays have:

 

0-5,

6-10,

11-15,

15+ plays

 

I would probably expect to see it in the form of a 100% Stacked bar chart or a Pie chart.

 

 

Here is the data model:

 

 

'mediaKeyLinkTable' table has a list of unique videos. 

 

 

 

'AppInsights' table has a description of every single play that took place:

 

 

 

To group the plays by video names is very easy:

 

 

However, I do not know how to bin (group) the numbers that I get in the table by the criteria mentioned in the very beginning of the post.

Need Help
  • Anonymous's avatar
    Anonymous
    8 years ago

    Anonymous I ended up adding 2 calcs in the AppInsights table

     

    1) Calculated Column

    Number of Plays = CALCULATE( COUNT(AppInsights[MediaKey]), ALLEXCEPT(AppInsights, AppInsights[MediaKey]))

    2) Plays Bin = SWITCH (TRUE(),
    [Number of Plays] > 0 && [Number of Plays] <= 5, "0-5",
    [Number of Plays] > 5 && [Number of Plays] <= 10, "6-10",
    [Number of Plays] > 10 && [Number of Plays] <= 15, "10-15",
    [Number of Plays] > 15, "15+")

     

    And this works and gets filtered as well. I think its exactly the same without the need to create the calc table.

10 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    Anonymous I think what you are looking to create is bins.  here is a link. https://docs.microsoft.com/en-us/power-bi/desktop-grouping-and-binning

     

    Also you could create a calculated column using switch statement as for legends you need to create calculated columns as measures wont work.

     

    SWITCH(TRUE(),

    [NoOfPlays] > 0 && [NoOfPlays]<= 5,"0-5",

    [NoOfPlays] > 5 && [NoOfPlays] <= 10, "6-10",

    [NoOfPlays] > 10 && [NoOfPlays] <= 15, "11-15",

    "15+")

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      Thanks, Anonymous! 

      1) I will not be able to use the first way, as the grouping (binning) method allows to select videos manually only. Whereas, I need it to be done automatically by the required groups (0-5, 6-10 and so on).

       

      Below I have selected the last video and the one that has 15 plays. : )

       

       

      2) The second method is good; however, [NoOfPlays] for each of the videos has to be calculated in some way beforehand. 

      Otherwise, it allocates every play to '0-5' category, considering it as a video with one play.

       

       

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        8 years ago

        Anonymous isn't the play time in the second screenshot the number of plays?

         

        if you don't have a list of videos with number of plays, I would suggest creating a calculated table with video name and number of plays. 

          

        1) I would create a new column first with plays per video like

         

        CALCULATE(DISTINCTCOUNT(PLAY ID), ALLEXCEPT(Video Name Column))

         

        The distinct calculation can change based on how you calculate number of plays. 

         

        2) Then I would create a calculated table with distinct values from video name column and number of plays column.

         

        CALCULATED TABLE