Combining Categories < 2% when category is date.

MelissaJPF27 — Thu, 23 Jan 2025 10:09:44 GMT

Hi,

I am looking to create a graph like the below but combining any values less than 2% into an 'Others' category (in this case, combining 2015, 2019 & 2020 together).

This is a snippet of my data - it needs to be a unique count as the same reference number may have multiple entries but the date is always the same per ref number. There may be multiple references with the same date. I only need it to be split by year and not day or month. I have 13521 rows and 46 columns in my file currently so this is just a sample but I think covers the key details for what is needed to make this graph - I just need something to combine values for the smaller years so they can be clearly shown on the graph.

I have tried a few different commands & methods but haven't found a solution yet.

Reference_Number	Date_Created	Source	Status
1	23/10/2019	O	Open
1	23/10/2019	O	Open
1	23/10/2019	O	Open
1	23/10/2019	O	Open
1	23/10/2019	O	Open
1	23/10/2019	O	Open
2	08/09/2020	O	Open
3	23/10/2020	O	Open
4	24/11/2020	O	Open
5	19/02/2021	O	Open
5	19/02/2021	O	Open
5	19/02/2021	O	Open
5	19/02/2021	O	Open
6	20/12/2021	O	Open
6	20/12/2021	O	Open
6	20/12/2021	O	Open
6	20/12/2021	O	Open
7	03/01/2022	O	Open
7	03/01/2022	O	Open
7	03/01/2022	O	Open
7	03/01/2022	O	Open
7	03/01/2022	O	Open
8	21/01/2022	O	Open
8	21/01/2022	O	Open
8	21/01/2022	O	Open
8	21/01/2022	O	Open
9	10/02/2022	O	Open
9	10/02/2022	O	Open
9	10/02/2022	O	Open
9	10/02/2022	O	Open
10	21/03/2022	O	Open
10	21/03/2022	O	Open
10	21/03/2022	O	Open
10	21/03/2022	O	Open
10	21/03/2022	O	Open
10	21/03/2022	O	Open
11	25/03/2022	O	Open
11	25/03/2022	O	Open
11	25/03/2022	O	Open
11	25/03/2022	O	Open
11	25/03/2022	O	Open
12	04/05/2022	O	Open
13	30/08/2022	O	Open
14	24/04/2023	O	Open
15	25/04/2023	O	Open
16	19/06/2023	O	Open
17	07/07/2023	O	Open
18	19/10/2023	O	Open
19	19/10/2023	O	Open
20	08/11/2023	O	Open

Thank you

Re: Combining Categories < 2% when category is date.

johnt75 — Thu, 23 Jan 2025 10:27:44 GMT

You can use the principles described in https://www.sqlbi.com/articles/filtering-the-top-products-alongside-the-other-products-in-power-bi/

Re: Combining Categories < 2% when category is date.

bhanu_gautam — Thu, 23 Jan 2025 10:29:42 GMT

MelissaJPF27 , Create a new column for Year

Year_Created = YEAR([Date_Created])

Use the following DAX formula:

YearPercentage =
VAR TotalCount = COUNTROWS('YourTable')
VAR YearCount = COUNTROWS(VALUES('YourTable'[Reference_Number]))
RETURN DIVIDE(YearCount, TotalCount, 0)

Create a new column to categorize years with less than 2% as 'Others:

DAX
YearCategory =
IF(
[YearPercentage] < 0.02,
"Others",
FORMAT([Date_Created], "yyyy")
)

topic Re: Combining Categories < 2% when category is date. in DAX Commands and Tips

Combining Categories < 2% when category is date.

Re: Combining Categories < 2% when category is date.

Re: Combining Categories < 2% when category is date.