Distinct count of number based on sales amount

[irenelitw629]

Hi everyone,

I have one set of sales date as per below example. I want to create a measure to get distinct count of opportunity parent when the sales amount is  >=$1m.

Sales quarter	Opportunity Parent number	Opportunity Number	Product	Sales Amount
22-Q2	OPTY5071363	OPTY5071363	TF	$ 1,638,334
22-Q2	OPTY5052097	OPTY5052097	IM	$ 405,441
22-Q2	OPTY5052097	OPTY5075331	IM	$ 800,000
22-Q1	OPTY5080075	OPTY5080075	CF	$ 1,500,000

I have sales quater and product filter in the visualization - so the filter needs to apply to the measure as well.

For example, when no sales quarter is selected, the distinc parent number count should be 3.

When IM product is selected, it should be 1 distinc parent number count.

Sales quarter	Opportunity Parent number	Opportunity Number	Product	Sales Amount
22-Q2	OPTY5052097	OPTY5052097	IM	$ 405,441
22-Q2	OPTY5052097	OPTY5075331	IM	$ 800,000

Any idea what should be the measure? I have tried filter all, Allselected, group by but it is not working.

Here is the sameple file

https://drive.google.com/file/d/1MeOON5tvU4ARZjRLoUOy6GPFOZMnpDlr/view?usp=sharing

Thanks!!

[irenelitw629]

Thanks Amit. It works but only when account name is in the table. Is there anyway to show this as scorecard without pulling account name in the table?

[amitchandak]

@irenelitw629 , Try measure like

calculate( countx(filter(summarize(Table, Table[Opportunity Parent number], "_1", sum(Table[Sales Amount])), [_1] > 1000000),[Opportunity Parent number]) ,
filter(allselected(Table) , Table[Opportunity Parent number] = max( Table[Opportunity Parent number])))