Problem with calculating values

[Quest]

There is a calculation I am trying to achieve:

The data in my report is something like below:

Date	Product	Type	#Subscribed
7/1/2021	A	Web	2
7/1/2021	A	Desktop	5
7/1/2021	B	Web	10
7/1/2021	B	Desktop	4
7/1/2021	C	Web	9
7/1/2021	C	Desktop	2
7/1/2021	D	Web	10
7/1/2021	E	Web	1
8/1/2021	A	Web	10
8/1/2021	B	Web	10
8/1/2021	B	Desktop	5
8/1/2021	C	Web	9
8/1/2021	D	Desktop	4
8/1/2021	D	Desktop	3
8/1/2021	E	Web	1

I have to find the number of Subscribers for each product in each month irrespective of the Type. Something like below

Date	Product	Type	#Subscribed	#SubscribedIrrespectiveofType
7/1/2021	A	Web	2	7
7/1/2021	A	Desktop	5	7
7/1/2021	B	Web	10	14
7/1/2021	B	Desktop	4	14
7/1/2021	C	Web	9	11
7/1/2021	C	Desktop	2	11
7/1/2021	D	Web	10	10
7/1/2021	E	Web	1	1
8/1/2021	A	Web	10	10
8/1/2021	B	Web	10	15
8/1/2021	B	Desktop	5	15
8/1/2021	C	Web	9	9
8/1/2021	D	Desktop	4	7
8/1/2021	D	Desktop	3	7
8/1/2021	E	Web	1	1

The final report should show only a count of products against each month and each type which has more than 10 subscribers.

Date	Product	Type	#Subscribed	#SubscribedIrrespectiveofType
7/1/2021	B	Web	10	14
7/1/2021	B	Desktop	4	14
7/1/2021	C	Web	9	11
7/1/2021	C	Desktop	2	11
7/1/2021	D	Web	10	10
8/1/2021	A	Web	10	10
8/1/2021	B	Web	10	15
8/1/2021	B	Desktop	5	15

Final Result:

Date	Type	# of Product
7/1/2021	Web	3
8/1/2021	Web	2
7/1/2021	Desktop	3
8/1/2021	Desktop	2

Please help with how I can acheive this.

[smpa01 · Accepted solution]

@Quest  please try this

Measure =
CALCULATE (
    CALCULATE (
        DISTINCTCOUNT ( 'Table'[Product] ),
        CALCULATETABLE (
            'Table',
            FILTER (
                ADDCOLUMNS (
                    'Table',
                    "@count",
                        CALCULATE (
                            SUM ( 'Table'[#Subscribed] ),
                            ALLEXCEPT ( 'Table', 'Table'[Date], 'Table'[Product] )
                        )
                ),
                [@count] >= 10
            )
        )
    ),
    ALLEXCEPT ( 'Table', 'Table'[Date] )
)

[Quest]

Thanks a lot. This works.

[Quest]

Thank you for the response but this does not work in my case. In my final output, I want the #of products repeated for each Type for a month. In this case for 7/1/2021, the count of distinct products is 3 and this value should be shown against each Type in the Month, as shown below.

Date	Type	# of Product
7/1/2021	Web	3
7/1/2021	Desktop	3
8/1/2021	Web	2
8/1/2021	Desktop	2

[Jihwan_Kim]

Hi,

I am not sure if I understood your question correctly, but check the below picture and the attached pbix file.

No. of Product : =

VAR newtable =

FILTER (

ADDCOLUMNS (

VALUES ( Data[Product] ),

"@subscribedIrrespectiveofType", CALCULATE ( SUM ( Data[#Subscribed] ), ALL ( Data[Type] ) )

),

[@subscribedIrrespectiveofType] >= 10

)

RETURN

IF ( HASONEVALUE ( Data[Date] ), COUNTROWS ( newtable ) )