Getting percentage count by days

[dollaratneu]

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

[parry2k]

@dollaratneu sorry not fully clear what you are looking for, can you provide but more details.

[dollaratneu]

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 2

[v-yingjl · Accepted solution]

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.