topic Dealing with percents in DAX Commands and Tips

Dealing with percents

majid154a — Sun, 21 Jul 2024 08:36:10 GMT

I have data containing the following columns: Category, Sub-Category, Order ID, and Country.

I want to visualize this data using a Matrix chart such that:
- Category and Sub-Category are in the Rows.
- Country is in the Columns.
- The values represent the Order Count.

Requirements:
1. How can I make the order percentages for each Category add up to 100%?
2. When performing a Drill Down on a specific Category to show the Sub-Categories, how can I ensure that the sum of all Sub-Categories equals 100%?
3. How can I ensure that the total percentages in the columns and rows add up to 100%?

What is the method to achieve this in Power BI?

see here convert numbers to percent:

Attaced data as example:

Order ID	Category	Sub-Category	Country
1	Furniture	Chairs	United States
2	Office	Paper	United States
3	Technology	Phones	United States
4	Furniture	Tables	United States
5	Office	Binders	United States
6	Technology	Laptops	United States
7	Furniture	Bookcases	United States
8	Office	Art	United States
9	Technology	Accessories	United States
10	Furniture	Furnishings	United States
11	Office	Labels	United States
12	Technology	Copiers	United States
13	Furniture	Chairs	United States
14	Office	Envelopes	United States
15	Technology	Phones	United States
16	Furniture	Tables	United States
17	Office	Storage	United States
18	Technology	Laptops	United States
19	Furniture	Bookcases	Denmark
20	Office	Supplies	Denmark
21	Technology	Accessories	Denmark
22	Furniture	Furnishings	Denmark
23	Office	Paper	Denmark
24	Technology	Copiers	Denmark
25	Furniture	Chairs	Denmark
26	Office	Art	Denmark
27	Technology	Phones	Denmark
28	Furniture	Tables	Denmark
29	Office	Binders	Denmark
30	Technology	Laptops	Denmark
31	Furniture	Bookcases	Denmark
32	Office	Labels	Denmark
33	Technology	Accessories	Denmark
34	Furniture	Furnishings	Denmark
35	Office	Envelopes	Denmark
36	Technology	Copiers	Vietnam
37	Furniture	Chairs	Vietnam
38	Office	Storage	Vietnam
39	Technology	Phones	Vietnam
40	Furniture	Tables	Vietnam
41	Office	Supplies	Vietnam
42	Technology	Laptops	Vietnam
43	Furniture	Bookcases	Vietnam
44	Office	Paper	Vietnam
45	Technology	Accessories	Vietnam
46	Furniture	Furnishings	Vietnam
47	Office	Art	Vietnam
48	Technology	Copiers	Vietnam
49	Furniture	Chairs	Vietnam
50	Office	Binders	Vietnam
51	Technology	Phones	Vietnam
52	Furniture	Tables	Vietnam
53	Office	Labels	Vietnam
54	Technology	Laptops	Vietnam
55	Furniture	Bookcases	Vietnam
56	Office	Envelopes	Vietnam
57	Technology	Accessories	Vietnam
58	Furniture	Furnishings	Vietnam
59	Office	Storage	Vietnam
60	Technology	Copiers	Vietnam
61	Furniture	Chairs	Vietnam
62	Office	Paper	Vietnam
63	Technology	Phones	Vietnam
64	Furniture	Tables	Vietnam
65	Office	Binders	Vietnam
66	Technology	Laptops	Vietnam
67	Furniture	Bookcases	Vietnam
68	Office	Art	Vietnam
69	Technology	Accessories	Vietnam
70	Furniture	Furnishings	Vietnam
71	Office	Labels	Vietnam
72	Technology	Copiers	Vietnam
73	Furniture	Chairs	Vietnam
74	Office	Envelopes	Vietnam
75	Technology	Phones	Vietnam
76	Furniture	Tables	Vietnam
77	Office	Storage	Vietnam
78	Technology	Laptops	Vietnam
79	Furniture	Bookcases	Vietnam
80	Office	Supplies	Vietnam
81	Technology	Accessories	Vietnam
82	Furniture	Furnishings	Vietnam
83	Office	Paper	Kosovo
84	Technology	Copiers	Kosovo
85	Furniture	Chairs	Kosovo
86	Office	Art	Kosovo
87	Technology	Phones	Kosovo
88	Furniture	Tables	Kosovo
89	Office	Binders	Kosovo
90	Technology	Laptops	Kosovo
91	Furniture	Bookcases	Kosovo
92	Office	Labels	Kosovo
93	Technology	Accessories	Kosovo
94	Furniture	Furnishings	Kosovo
95	Office	Envelopes	Kosovo
96	Technology	Copiers	Kosovo
97	Furniture	Chairs	Kosovo
98	Office	Storage	Kosovo
99	Technology	Phones	Kosovo
100	Furniture	Tables	Kosovo

Re: Dealing with percents

BINewbie1 — Sun, 21 Jul 2024 11:07:51 GMT

Percentages in Power BI are achieved obviously with the division of a numerator by a denominator.

The DIVIDE function is key, but the harder part (as least as i've been learning) in Power BI is understanding a concept called filter context - basically how the tables are populated. For the denominator to work correctly as you want you'll need to remove filters being applied to the numerator. Which filters depend on what you want to divide by. This will be acheived using the REMOVEFILTERS function.

This will likley involve removing the filters that apply to country (to give a total of all the orders across countries) and removing the filters on subcategory. To get each one to add up to 100%, probably need to use two different measures.

Here's a really helpful video explaining filter context from the nice people as SQLBI:

https://www.youtube.com/watch?v=L5WR-imfyYI

Hope that helps point you in the right direction.

Adam

Re: Dealing with percents

Anonymous — Sun, 21 Jul 2024 16:56:47 GMT

Source Data

Sub Category order id count formula

Count % formula

Matrix visual elements

Re: Dealing with percents

Anonymous — Tue, 23 Jul 2024 08:53:22 GMT

Hi majid154a ,

Thanks for the reply from Anonymous and BINewbie1 , please allow me to provide another insight:

You can try this measure expression.

MEASURE = VAR _count = COUNT ( 'Table'[Order ID] ) VAR _all = CALCULATE ( COUNT ( 'Table'[Order ID] ), ALLSELECTED ( 'Table' ) ) RETURN DIVIDE ( _count, _all )

If your Current Period does not refer to this, please clarify in a follow-up reply.

Best Regards,

Clara Gong

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

Re: Dealing with percents

majid154a — Sun, 28 Jul 2024 05:21:01 GMT

Thank you for helping Anonymous

In the image below, I need the red circle to represent 100%. Additionally, I need each yellow circle to also represent 100%.

How can we achieve this?

Re: Dealing with percents

Anonymous — Mon, 29 Jul 2024 09:15:20 GMT

Hi majid154a ,

Modify measure.

MEASURE = VAR _count = COUNT ( 'Table'[Order ID] ) VAR _all = CALCULATE ( COUNT ( 'Table'[Order ID] ), ALLSELECTED ( 'Table'[Sub-Category]) ) RETURN DIVIDE ( _count, _all )

If your Current Period does not refer to this, please clarify in a follow-up reply.

Best Regards,

Clara Gong

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