how to count distinct values in a column

[techtina]

Hello all!

I'm trying to create a column that shows the total balance of each account. For example, I want the total balance of account 7055 and 8012. How do I go about doing that when I have multiple balances for each account? (There are also 65 seperate accounts with over 39,000 rows, I just cut & pasted a small portion)

[Nhallquist]

Before we provide several possible solutions, we should understand what you are planning to do with this data and how you plan to use it.  Are you wanting to create a Table in the data model that will have a single balance for each account?  Or...  are you just looking to display a single balance for each account back to a user?  Are their any other details you can share with us?

Let us know,

Nate

[techtina]

@Nhallquist

So far I was able to count the rows of each account by creating a new column: Count = 1 and then a new measure: Total = COUNT(FactBalance[Count]. I then created a new table that listed the account code, total (rows), and balance of each account. 

However, I need the total balances listed in another column  in order to calculate the percentage and create my desired visualizations.

Any advice will be much appreciated! 

[Nhallquist]

Quick Question...  Does the Sum of the Balance column euqal the Total Balance?

[techtina]

The Sum of the balances of all the accounts together does equal the Total Balance. This is what I was able to get so far using COUNT and creating a TOTAL measure:

[Nhallquist · Accepted solution]

Here is my solution.  Not sure it meets your needs exactly, so let me know if you need more help.  

Key formula for % of total:  Percent of Total Balance = Sum(Sheet1[Balance])/Calculate(Sum(Sheet1[Balance]),All(Sheet1))

You can use the Acct. dimension i  Pie Chart.  Then, create a new Measure, and use the formula above to create % of Total.  This works because your individual Balance entries sum to the Total Balance.  You don't actually need the Total Balance column, or the Count of Rows for this.

If you want the PBIX file and my data source, let me know and I can email it to you, or something.

Nate

[techtina]

 Hi Nate,

Would you kindly email me your PBIX file and data source? I'll send you my email via private message. 

Thanks

-Tina

[techtina]

Ultimately I want to calculate the percentage of the total balance of each account (7055, 8012, ect) to the total balances of all accounts ($186,542,728.32). I want to create a visualization such as a bar chart where the percentage of each account can be seen on a trend line. This is a project for work where I am analyzing expense accounts.