Count distinct ID's with aggregate balances

[zge04]

Hello, I am working on a pivot table that summarizes accounts of clients and client ID's by brackets of available balances, i.e. accounts with balances less than $3,000 are grouped together, accounts with balances between $3000 and $10,000 are grouped together,etc. The main Data table I have, lists the accounts of all clients, their respective balances, and the respective client ID's (each ID might have several accounts). I manually created a table which has two columns: "Brackets Column" and "Bracket Code". I added a calculated column named "Bracket Code" to the Data Table and created a relationship between both tables using the aforementioned calculated column. Currently I am using pivot table to summarize the number of distinct account numbers and distinct client ID's under each category. Pivot table will have the following columns: Column 1 Column2 Column3 "Bracket Category" count of distinct account numbers count of distinct ID's Column2 is being calculated easily by creating the measure= Count(Distinct(Account Numbers)) However, for client ID's it isn't that simple. When am using the same measure for ID's, the same ID is counted under more than one Bracket Category. Could anyone help on the syntax of the measure which would count each ID once under its designated "Bracket Category" Thanks

[MFelix]

Please see this post regarding How to Get Your Question Answered Quickly (courtesy of @Greg_Deckler) and How to provide sample data in the Power BI Forum (courtesy of @ImkeF).

Can you please share a mockup data or sample of your PBIX file. You can use a onedrive, google drive, we transfer or similar link to upload your files.

If the information is sensitive please share it trough private message.

[zge04]

Please find below sample data

many thanks in advance

bracket            bracket code

<1000	a
<3000	b
<5000	c
<7000	d
<10000	e
>10000	f

accountnbr client id    balance         bracket code

30	x	840	a
96	x	2600	b
89	x	100000	f
30	y	8600	e
56	y	11000	f
82	z	450	a
19	w	79000	f
45	w	8800	e
46	w	9000	e
40	w	910	a
88	v	3700	c
42	u	6300	d
4	u	680	a
18	t	8400	e
98	t	7100	e

Bracket      Bracketcode   Count of account nbr  countids

<1000	a	4	??
<10000	e	5	??
<3000	b	1	??
<5000	c	1	??
<7000	d	1	??
>10000	f	3	??

[Ashish_Mathur]

Hi,

Please show the expected result in the column with ??