Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Custom Matrix for displaying data as cohort visualization

Dear all,

I am having a table on Power BI having the structure like this:

 

I want to use matrix and custom it to receive the desired output like this one (it can be understood as cohort visualization):

 

Until now, I can only choose the attribute for matrix as follow:
Rows: Week

Columns: Index

Value: Chosen members
-> That means the column "Members" cannot be shown on that matrix.

How can I custom or perform any DAX or alternative methods to visualize as my desired output to matrix on Power BI?

Thanks a lot.

Need Help
  • Priya1969's avatar
    Priya1969
    3 years ago

    Thanks!   Once you added, 'Week' and 'Total members' in the row,

    1. Select the format pane, expand the row headers, off the stepped layout.

    2. On the top, select Data/Drill option, then click the expand next level button.

    You will get the desire output.

     

     

     

     

10 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    3 years ago

    Anonymous , Create a new table with Values  1,2,3 and total as Text and another column as 1,2,3,0 (as the sort column)

     

    Join it with the index and then change the formula for total

    refer

    Power BI - Show Grand total row or Avg Grand total row in Visual: https://youtu.be/OSwRZ4GUhxc

    (You should able to do with one dimension you do not need 2nd dimension)

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Thanks for your suggestion. But I have some comments as below:
      1. I think that it would not be an optimal solution since the data must be changed / updated day by day, which means the index will be always increased, and we cannot know the exact number of indexes to create in the temporary table. So performing hard-coding the temporary table should not be the way to solve this problem.

      2. There's no need to perform any calculations or change the formula since the column "Members" has already been calculated in the data. My problem is just how to transform the data that I have in the table given into the desired output.

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Yes, I can use this view. I guess you are performing group by with two attributes right?

      • Priya1969's avatar
        Priya1969
        Resolver I
        3 years ago

        Thanks!   Once you added, 'Week' and 'Total members' in the row,

        1. Select the format pane, expand the row headers, off the stepped layout.

        2. On the top, select Data/Drill option, then click the expand next level button.

        You will get the desire output.

         

         

         

         

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi Anonymous ,

     

    You can use the ISINSCOPE function to specifically return the value of Subtotal.

    ISINSCOPE function (DAX) - DAX | Microsoft Learn

    For example, 1 can put Chosen Member in it, and 0 can put Total Member in it.

    Measure = IF(ISINSCOPE('Table'[Year]),1,0)

     

     

     

    Best Regards,

    Stephen Tao

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Thanks for your suggestions. But the column "Members" does not reflect the subtotal of all the index value in that row. And one more thing to consider is that it does not need to be calculated, since it has already had values inside.