Help with a summary of date (count a day)

[bdeleur]

Hi, I have the following problem. I have a table that shows a value for each hour, each day, and each department. These values need to be summed up. I am able to create a matrix that shows the sum per day, per month. However, I would also like to count how often a value has occurred per month.

Below a short summary of the data. Of course it continues.

The categories are <5, 5><15, 15><25, >25

Date	department	Count
1-1-2024	Afd A	7
1-1-2024	Afd B	1
1-1-2024	Afd C	1
1-1-2024	Afd D	4
1-1-2024	Afd E	5
1-1-2024	Afd F	0
1-1-2024	Afd G	2
1-1-2024	Afd H	8
1-1-2024	Afd I	0
2-1-2024	Afd A	6
2-1-2024	Afd B	0
2-1-2024	Afd C	0
2-1-2024	Afd D	1
2-1-2024	Afd E	3
2-1-2024	Afd F	1
2-1-2024	Afd G	0
2-1-2024	Afd H	9
2-1-2024	Afd I	0
3-1-2024	Afd A	2
3-1-2024	Afd B	1
3-1-2024	Afd C	0
3-1-2024	Afd D	0
3-1-2024	Afd E	0
3-1-2024	Afd F	0
3-1-2024	Afd G	0
3-1-2024	Afd H	3
3-1-2024	Afd I	1

In excel i'm able to create the following matrix:

Rijlabelsjanuarifebruarimaartaprilmei

1	28	7	20	6	14
2	20	3	25	4	2
3	7	3	24	-4	7
4	11	11	19	10	12
5	3	5	16	15	16
6	6	-2	20	12	-1
7	12	3	41	2	10
8	11	0	25	3	15
9	6	6	24	4	27
10	7	9	22	17	14
11	0	7	15	24	19
12	9	2	9	25	10
13	3	8	19	11	2
14	1	11	10	22	3
15	1	7	14	20	5
16	2	9	9	9	12
17	1	15	16	11	27
18	0	17	11	19	33
19	1	10	2	19	32
20	11	11	5	18	26
21	7	35	4	16	15
22	5	37	11	19	14
23	0	15	19	-1	18
24	8	27	14	7	18
25	9	22	13	13	25
26	8	23	17	17	28
27	10	22	18	22	14
28	8	19	24	14	5
29	0	22	27	9	2
30	3		20	16	8
31	3		18		22

A month summary looks like this: it means 13 times less then 5, 16 times between 5 and 15, etc : 

	jan	feb	mar	apr	may
<5	13	6	2	6	5
5><15	16	12	9	10	12
15><25	1	8	16	13	7
>25	1	3	4	1	7

I don't want the step with excel and want to do it in PowerBI directly but the last month summary is something I don't get fixed.

I hope somebody can help me

Gr

Bart

[Ashish_Mathur]

Hi,

I have solved a similar problem in the attached file.

Hope this helps.

[mickey64]

Step 0: I use your data.

    Date: yyyy/mm/dd

Step 1: I add a 'Category' column to the table, below.

    Category = IF([Count]<5,"<5",IF([Count]<15,"5><15",IF([Count]<25,"15><25",">25")))

Step 3: I make 2 matrixs below.

[bdeleur]

Hi @mickey64 ,

Tnx for your answer. I followed your steps. De first matrix is no problem and validates with my excel. But when I created the second matrix something stranges happends. I get a totally different result:

Not al category's are present and the count doesn't fit the result I posted above. This is exactly the problem I encouterd.

The table we use for is a summary:

tabel = calculatetable(summarize(tabeloriginal, value, location, department, calender[date]), our = 6, filer for the departments))

Is that a problem?

[mickey64]

In the second matrix, I use 'Count' of Category.

Do you use 'Sum' of Count?

[bdeleur]

Count, but the strange thing is that not all categories are showed. 

[mickey64]

Categories with no data are not displayed in the matrix, but is there any data that is not being displayed?

[bdeleur]

Yes I know. When I put the output in another database and use your calculated column it works perfectly. Ignore the sorting, but this is how the result must be.

So what makes it different when you use the output versus the original source. Do I need to make a new summary tabel? Will that fix the problem?

[mickey64]

Does 'Totaal van Category' mean 'Count of Category'? 

[bdeleur]

Hi, Yes Totaal is "aantal" so it means count. Not unique. Other choises are first and last.

[mickey64 · Accepted solution]

I count daily 'raw' DATA.

You count daily ’Total’ DATA.

Please make 2 measures and a Table below.

M_SUM Count = SUM(DATA[Count])

M_Category = IF([M_SUM Count]<5,"<5",IF([M_SUM Count]<15,"5><15",IF([M_SUM Count]<25,"15><25",">25")))

Table = SUMMARIZE('DATA',DATA[Date],"SUM Count",[M_SUM Count],"Category",'DATA'[M_Category])

[bdeleur]

Hi @mickey64 ,

YES this is it. Super. Now the count is perfect. You are wonderfull.

Thank you very much.

Greetings Bart

[foodd]

Please remember to adhere to the decorum of the Community Forum when asking a question.

Please provide your work-in-progress Power BI Desktop file (with sensitive information removed) that covers your issue or question completely in a usable format (not as a screenshot). You can upload the PBIX file to a cloud storage service such as OneDrive, Google Drive, Dropbox, or to a Github repository, and then share a file’s URL.

https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

This allows members of the Forum to assess the state of the model, report layer, relationships, and any DAX applied.