Percent of total units

[Anonymous]

Hi all

I am having issues with something that should be simple. I need to display the percentage of the total in a bar chart.

My data looks like this:

ID	Date	Filter 1	Filter 2	Comment 1	Comment 2
1	4/21/2022
2	4/21/2022
3	4/21/2022			abcd	efg
4	4/21/2022			abcd	efg
5	4/21/2022
6	4/21/2022			hijk	lmnop
7	4/21/2022			hijk	lmnop
8	4/21/2022			hijk	lmnop
9	4/22/2022
10	4/22/2022			abcd	efg
11	4/22/2022
12	4/22/2022
13	4/22/2022
14	4/22/2022			abcd	efg
15	4/22/2022
16	4/23/2022
17	4/23/2022
18	4/23/2022
19	4/23/2022			hijk	lmnop
20	4/23/2022
21	4/23/2022
22	4/23/2022
23	4/23/2022			hijk	lmnop
24	4/23/2022
25	4/23/2022
26	4/23/2022
27	4/23/2022
28	4/23/2022			abcd	efg
29	4/23/2022			abcd	efg
30	4/23/2022			abcd	efg
31	4/24/2022
32	4/24/2022			abcd	efg
33	4/24/2022
34	4/24/2022
35	4/24/2022			abcd	efg
36	4/24/2022			abcd	efg
37	4/24/2022			abcd	efg
38	4/24/2022
39	4/24/2022			hijk	lmnop
40	4/24/2022
41	4/24/2022
42	4/24/2022			abcd	efg
43	4/24/2022			abcd	efg
44	4/24/2022			abcd	efg
45	4/24/2022
46	4/24/2022
47	4/24/2022			abcd	efg
48	4/24/2022			abcd	efg
49	4/24/2022			abcd	efg
50	4/24/2022

Now, what I need to accomplish is this. Once a date or date range is selected via date slicer:

1: Total/ day: distinctcount(ID).

Good done.

2: I need a bar chart that displays: out the daily x number of ID's, what percentage of TOTAL is comment 1:

My problem is, I can't reliably calculate (and keep) the value of the DailyTotal.

I tried an intermediary table and doing a CALCULATE and send everything I need as a filter, calculation is correct on the page, once I add it to the chart, it "recalculates" based on category (tables are NOT linked, so actually stumped how)

Tried this: Solved: Trouble with Percent of Overall Total - Microsoft Power BI Community, but unfortunately that example does not take into consideration blanks.

Also tried:

DIVIDE(DISTINCTCOUNT(ID),CALCULATE(Distinctcount(ID),Removefilters(table[Filter1))

DIVIDE(DISTINCTCOUNT(ID),CALCULATE(Distinctcount(ID),ALL(table[Filter1))

They all kinda work ok, but, as soon as I remove the blanks from my BarChart, it also redoes the total ID count, and that is incorrect for my use case.

So basically I am trying to show in the bar chart :

for the date of 4/21/2022, I have a total of 8 ID's, so I expect to see that since abcd has 2 values, it's 25% and hijk with 3 values is 37.5%. 

The blanks WILL NOT be displayed, but still need to be a part of the daily count.

Thanks for any help, been driving me up the wall this one.

[v-rzhou-msft]

Hi @Anonymous ,

I think you can try this code to create a measure.

Measure = 
VAR _TOTAL = CALCULATE(DISTINCTCOUNT('Table'[ID]),ALLEXCEPT('Table','Table'[Date]))
RETURN
DIVIDE(CALCULATE(DISTINCTCOUNT('Table'[ID])),_TOTAL)

Result is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thank you Rico for your reply, unfortunately, doesn't work for me for ... reaons I don't understand, but it has to do with the blanks. 
Please take a look at how it's behaving in my dashboard (but weridly, not in yours):

So, we have 408 units total for the given day. 

Out of those, we have 402 blanks, and 1 ABCD and 5 HIJK.

If you do the math, it's correct. 1 out of 408  = 0.25 ad 5 out of 408 = 1.2254 ... so correct.

And the blanks are the rest of 98.53%

Now, in the picture below, I just removed the blanks:

As you can see, the result now only reflects the 7 units remaining after the filter has been applied. 
This is the behavior I am trying to overcome but failing to, but curiously, works in yours as I expect it 🤔 

[v-rzhou-msft]

Hi @Anonymous ,

I think you can download my attatchment and compare mine data model with yours. If this still couldn't help you solve your problem, please share a sample file with me and show me a screenshot with the result you want. This will make it easier for me to find the solution.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hello again

Of course I downloaded any looked at your data file, that is why I am perplexed by the results.

Let me come at this from another angle. In your file, you have a Filter on the left visual where you take out the Blanks. That is exactly what I am doing, but for me, it recalculates the percentage (same formula you used).

That is what I am trying to explain in the table. It's literraly the Chart data visualized as a table.

Would like to upload the PBIX, but it won't let me