topic Problem with calculating values in DAX Commands and Tips

Problem with calculating values

Quest — Tue, 12 Oct 2021 00:49:31 GMT

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.

Re: Problem with calculating values

Jihwan_Kim — Tue, 12 Oct 2021 03:11:10 GMT

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 ) )

Re: Problem with calculating values

Quest — Tue, 12 Oct 2021 12:45:49 GMT

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

Re: Problem with calculating values

smpa01 — Tue, 12 Oct 2021 13:46:52 GMT

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] ) )

Re: Problem with calculating values

Quest — Tue, 12 Oct 2021 19:30:18 GMT

Thanks a lot. This works.