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
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.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsPlease 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 | ?? |
Hi,
Please show the expected result in the column with ??