Count Excesses and Defects with pooled data

[Syndicate_Admin]

Dear, I am writing to you asking for help with the problem I am having.

To make it better understood, I made a small query with fictitious data that I think can serve (the query is called Example)

Group	Product	Day	Detour
Group 2	Product 1	1	2
Group 2	Product 2	1	1
Group 2	Product 3	1	3
Group 2	Product 1	2	-1
Group 2	Product 2	2	-1
Group 2	Product 3	2	-2
Group 2	Product 1	3	2
Group 2	Product 2	3	1
Group 2	Product 3	3	-1
Group 1	Product 4	1	3
Group 1	Product 5	1	2
Group 1	Product 4	2	-1
Group 1	Product 5	2	-1

My intention is to create two matrix tables, in the first I would have the Group and Product in the rows, the days in the columns and the sum of the deviations as values while in the second I would have the Group and Product in the rows and as values two calculated Measures, one to count the number of days 'in Excess' (when the sum of the variations of each day is positive) and another that counts the days 'in Default' (when the sum of the variations is negative). I made the second table with a matrix because my intention is to put it next to the first and that they can be visualized as if it were one.
The measurements in the second table are:

# Excesses = CALCULATE(DISTINCTCOUNT(Example[Day]),Example[Detour]>0)

# Defects = CALCULATE(DISTINCTCOUNT(Example[Day]),Example[Detour]<0)

I leave an image with what I get.
Vista del resultado obtenidoView of the result obtained

As you can see, for group 1 it tells me that I have an excess and a defect and that corresponds to the data on the left, however for group 2 it tells me that I have 2 excesses and 2 defects and that is incorrect, since I only have 3 days. In fact looking at the table on the left you can see that it should have 2 excesses, but only 1 defect. I understand that the problem comes from the way to count the excess and defect but I do not know how to fix it (I count the days when the product is in excess or defect and if we see the table, for group 2 I have a defect on day 3, although the total is excess),

I leave an image of the same table but with the detail by group

I have a fairly simple example file, but the page is giving me enough error to upload it in case it helps solve the problem.

From now on thank you very much for everything.

Best regards

Gustavo

[Ashish_Mathur]

You are welcome.

[Ashish_Mathur · Accepted solution]

Hi,

Write these measures

Detour value = SUM(Data[Detour])

Excesses = COUNTROWS(FILTER(VALUES(Data[Day]),[Detour value]>0))

Defects = COUNTROWS(FILTER(VALUES(Data[Day]),[Detour value]<0))

Hope this helps.

[Syndicate_Admin]

Thank you very much for your solution! You saved me

[Ashish_Mathur]

Please mark my reply as Answer - the reply which carries the solution.