Forum Discussion
Getting percentage count by days
I've a dataset as under:
| date | days |
| 2020-01-15 12:13 | 1 |
| 2020-01-15 10:48 | 1 |
| 2020-01-15 10:45 | 2 |
| 2020-01-15 9:51 | 3 |
| 2020-01-15 9:52 | 3 |
| 2020-01-15 9:07 | 5 |
| 2020-01-15 11:43 | 2 |
| 2020-01-15 12:46 | 7 |
| 2020-01-15 13:12 | 2 |
| 2020-01-15 9:58 | 2 |
| 2020-01-15 11:29 | 2 |
| 2020-01-15 10:38 | 2 |
| 2020-01-15 11:21 | 2 |
| 2020-01-15 12:42 | 2 |
| 2020-01-15 14:32 | 2 |
| 2020-01-15 10:49 | 2 |
| 2020-01-15 15:33 | 2 |
| 2020-01-15 11:32 | 2 |
| 2020-01-15 10:50 | 2 |
I'm having a hard time to create a new table which would show the percentage of days where days<2 and percent of days where days >= 2
so the resulting table would look like this:
| Month | Days | % |
| Jan-20 | <2 | 10 |
| Jan-20 | >2 | 99 |
| Feb-20 | <2 | 20 |
| Feb-20 | >2 | 80 |
Hi dollaratneu ,
Based on your description, you can create a new calculated table like this:
New Table = VAR t1 = ADDCOLUMNS ( 'Table', "Month Days", SWITCH ( TRUE (), 'Table'[date].[MonthNo] = 1 && 'Table'[date].[Day] >= 1 && 'Table'[date].[Day] < 20, "Jan-20 <2", 'Table'[date].[MonthNo] = 1 && 'Table'[date].[Day] >= 20 && 'Table'[date].[Day] <= 31, "Jan-20 >2", 'Table'[date].[MonthNo] = 2 && 'Table'[date].[Day] >= 1 && 'Table'[date].[Day] < 20, "Feb-20 <2", 'Table'[date].[MonthNo] = 2 && 'Table'[date].[Day] >= 20 && 'Table'[date].[Day] <= 29, "Feb-20 >2" ), "Total Count", CALCULATE ( COUNT ( 'Table'[date] ), ALLEXCEPT ( 'Table', 'Table'[date].[MonthNo] ) ) ) VAR t2 = ADDCOLUMNS ( t1, "Count", SWITCH ( TRUE (), [days] < 2 && [Month Days] = "Jan-20 <2", COUNTX ( FILTER ( t1, [days] < 2 && [Month Days] = "Jan-20 <2" ), [date] ), [days] >= 2 && [Month Days] = "Jan-20 >2", COUNTX ( FILTER ( t1, [days] >= 2 && [Month Days] = "Jan-20 >2" ), [date] ), [days] < 2 && [Month Days] = "Feb-20 <2", COUNTX ( FILTER ( t1, [days] < 2 && [Month Days] = "Feb-20 <2" ), [date] ), [days] >= 2 && [Month Days] = "Feb-20 >2", COUNTX ( FILTER ( t1, [days] >= 2 && [Month Days] = "Feb-20 >2" ), [date] ) ) ) RETURN SUMMARIZE ( SUMMARIZE ( FILTER ( t2, [Count] <> BLANK () ), [Month Days], [Total Count], [Count], "%", [Count] / [Total Count] ), [Month Days], [%] )Attached a sample file in the below, hopes to help you.
Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
dollaratneu sorry not fully clear what you are looking for, can you provide but more details.
sure !
what I'm trying to do here is, get the percent of count of days for each month where the "days" column is less than 2 and likewise get the percent of count of days for each month where the "days" column is less than or equal to 2Hi dollaratneu ,
Based on your description, you can create a new calculated table like this:
New Table = VAR t1 = ADDCOLUMNS ( 'Table', "Month Days", SWITCH ( TRUE (), 'Table'[date].[MonthNo] = 1 && 'Table'[date].[Day] >= 1 && 'Table'[date].[Day] < 20, "Jan-20 <2", 'Table'[date].[MonthNo] = 1 && 'Table'[date].[Day] >= 20 && 'Table'[date].[Day] <= 31, "Jan-20 >2", 'Table'[date].[MonthNo] = 2 && 'Table'[date].[Day] >= 1 && 'Table'[date].[Day] < 20, "Feb-20 <2", 'Table'[date].[MonthNo] = 2 && 'Table'[date].[Day] >= 20 && 'Table'[date].[Day] <= 29, "Feb-20 >2" ), "Total Count", CALCULATE ( COUNT ( 'Table'[date] ), ALLEXCEPT ( 'Table', 'Table'[date].[MonthNo] ) ) ) VAR t2 = ADDCOLUMNS ( t1, "Count", SWITCH ( TRUE (), [days] < 2 && [Month Days] = "Jan-20 <2", COUNTX ( FILTER ( t1, [days] < 2 && [Month Days] = "Jan-20 <2" ), [date] ), [days] >= 2 && [Month Days] = "Jan-20 >2", COUNTX ( FILTER ( t1, [days] >= 2 && [Month Days] = "Jan-20 >2" ), [date] ), [days] < 2 && [Month Days] = "Feb-20 <2", COUNTX ( FILTER ( t1, [days] < 2 && [Month Days] = "Feb-20 <2" ), [date] ), [days] >= 2 && [Month Days] = "Feb-20 >2", COUNTX ( FILTER ( t1, [days] >= 2 && [Month Days] = "Feb-20 >2" ), [date] ) ) ) RETURN SUMMARIZE ( SUMMARIZE ( FILTER ( t2, [Count] <> BLANK () ), [Month Days], [Total Count], [Count], "%", [Count] / [Total Count] ), [Month Days], [%] )Attached a sample file in the below, hopes to help you.
Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
