Buckets Matrix of Days

[Anonymous]

I am trying to create a table and a matrix that shows the percentage change of an item to be in different days buckets, where the buckets are inclusive of each other:

Desired Outputs

Days	Count (Inclusive)
>0	20
>50	11
>100	6
>150	4
>200	2

This table should show the count of IDs the appear in each day bucket where Duration is greater than the bucket. Row 1 (Days >0) will contain all rows in the source table.

Then I want to create a matrix that shows the percantage (by count) of if IDs appear in both buckets:

Days	50	100	150	200
0	55%	30%	20%	10%
50	100%	55%	36%	18%
100		100%	67%	33%
150			100%	50%
200				100%

Row 1, means 55% appear in the >0 and the >50 buckets (11 / 20)

                       , 30% appear in the >0 and the >100 buckets (6 / 20)

                       , etc

SourceTable:

ID	DURATION (DAYS)
1	9
2	9
3	30
4	32
5	199
6	201
7	1
8	3
9	39
10	233
11	101
12	182
13	76
14	41
15	64
16	70
17	70
18	70
19	130
20	13

I have tried to create this dynamically but failed hard. My only solution so far is to create a measure for each calculation but then I can't it all to appear in 1 table and have to create a different table for each row of buckets.

e.g:  0 Count = COUNTROWS(DISTINCT(FILTER(SourceTable,SourceTable[Duration (Days)]>0)))

        50 Count = COUNTROWS(DISTINCT(FILTER(SourceTable,SourceTable[Duration (Days)]>50)))

        0 to 50 = [50 Count] / [0 Count]

Thanks

[v-yohua-msft · Accepted solution]

Hi, @Anonymous 

Start by creating calculated columns to calculate the rows that meet the criteria:

IsOver0 = IF(SourceTable[Duration (Days)] > 0, 1, 0)
IsOver50 = IF(SourceTable[Duration (Days)] > 50, 1, 0)
IsOver100 = IF(SourceTable[Duration (Days)] > 100, 1, 0)
IsOver150 = IF(SourceTable[Duration (Days)] > 150, 1, 0)
IsOver200 = IF(SourceTable[Duration (Days)] > 200, 1, 0)

Then create a new table, enter data:

Create a new measure to count rows:

Count = 
VAR _current_Days = SELECTEDVALUE('Table'[Days])
RETURN SWITCH(_current_Days,
    0,COUNTROWS(FILTER('SourceTable','SourceTable'[IsOver0]=1)),
    50,COUNTROWS(FILTER('SourceTable','SourceTable'[IsOver50]=1)),
    100,COUNTROWS(FILTER('SourceTable','SourceTable'[IsOver100]=1)),
    150,COUNTROWS(FILTER('SourceTable','SourceTable'[IsOver150]=1)),
    200,COUNTROWS(FILTER('SourceTable','SourceTable'[IsOver200]=1))
)

Then create a few measures to calculate their percentages and put them into the matrix:

Total Count = COUNTROWS(SourceTable)
0 = DIVIDE([Count], CALCULATE([Total Count], FILTER(SourceTable, SourceTable[IsOver0] = 1)))
50 = DIVIDE([Count], CALCULATE([Total Count], FILTER(SourceTable, SourceTable[IsOver50] = 1)))
100 = DIVIDE([Count], CALCULATE([Total Count], FILTER(SourceTable, SourceTable[IsOver100] = 1)))
150 = DIVIDE([Count], CALCULATE([Total Count], FILTER(SourceTable, SourceTable[IsOver150] = 1)))
200 = DIVIDE([Count], CALCULATE([Total Count], FILTER(SourceTable, SourceTable[IsOver200] = 1)))

How to Get Your Question Answered Quickly 

Best Regards

Yongkang Hua

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

[Anonymous]

Thank so much, everything works well! 

The only thing I had to change was the: 'SourceTable'[Count]>=4,BLANK() where I made it more dynamic to blank rather than based on the hard coded 4