Custom Matrix for displaying data as cohort visualization

[Anonymous]

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.

[Anonymous]

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]

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.

[Priya1969]

Are you ok with the below view?

[Anonymous]

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

[Priya1969 · Accepted solution]

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]

Thanks a lot. That's what I just did a few minutes ago ^^ I also turn off the +/- icons for preventing end-users from performing any other interactions.

But after expanding like that, are there any ways to customize the visualization of those expanded columns? For example, I want to highlight only the "Total members" column. I do not see any formats for supporting me to do this.

[Priya1969]

I don't think so we can do only highlight 'Total Column'. But we can do something like this.

[Anonymous]

Thanks anyway. That's okay if we cannot highlight the selected columns as mentioned above.

[amitchandak]

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

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.