Creating pie chart with counts of distinct values

[cmaso]

I'm new to BI, going through the tutorials and playing with it to learn as quickly as possible. I could use a hint on how to go about creating a simple pie chart that shows row counts from two tables (and their respective percentages of the combined total), and the same for row counts of total *distinct* values.

What I essentially have are 2 tables of appointments with clients that took place in 2017. Table 1 is for 1 location; table 2 is for the other location. I want a pie or donut chart where the whole pie represents the combined count of appointments from both tables, and the slices represent each table's percent of that total.

Also, many of the rows in those tables have the same client (identified by SSN). I'm ultimately looking for the number of unique clients in both tables, such that client is counted only once. And then I want to present the same kindof pie chart: the total number of distinct clients from both tables, and each table's percent of that total.

I know how to write the SQL queries to get these numbers, but not sure the best way to create the pie charts I want. Do I need to shape the data with functions or formulas first, maybe something along the lines of count(SSN) and count(distinct SSN)? Thanks much.

[cmaso]

Okay, I figured it out in part. I went to the modeling tab, selected "New Measure", and with with table 1 selected on the right, typed "table1TotalCount = COUNT(table1[SSN])". Then I repeated these steps with table 2. A new field was created in each table to hold these values, I dragged them onto the canvass and selected the "pie chart" visualization", and I got the pie chart I want.

Easy enough to get the count of distinct SSNs, too - just did the same as above, but used "DISTINCTCOUNT(table1[SSN])".

There's just one thing missing from the pie chart. When I scroll over one of the 2 slices, it displays the numeric count of that slice and the percent value of that slice. But nowhere in the chart does it display the grand total count of the whole pie. Is there a simple way to get that to display?

[v-piga-msft]

Hi @cmaso,

Have you solved your problem?

If you have solved, please always accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.

If you still need help, could you share your data model which could reproduce your scenario and the output you desired, so that we can help further investigate on it?

Best Regards,

Cherry

[Anonymous · Accepted solution]

@cmaso You could use the logic in the pic below to get the total. You could display it on a car in the center of a donut chart or a tooltip like I have done in the example below. The sum sales can be replaced by distinct count.