Percentage Column

[macgeorge]

Hi, I am having trouble with this table please see fig. a. It report view it shows this table with the wrong percentage totals for product 1 and 3.

Fig. a

I am using this code to work out the percentage and add a column:

% Compliant = ('Outcomes'[Spot Checks Compliant]/[Spot Checks Totals])

It is calculating the percentage based on the number of times the product number appears and then totalling the percentage. This is wrong. 

Fig. b - this is what the source data table looks like. 

I want the % Compliant field with the correct row percentages as shown here in fig. c:

Fig c - this shows the unique product number with the correct percentage compliant:

In fig.a it is presenting the product numbers correctly, but its adding the percentages together instead of consolidating the product number and then providing an overall percentage like in fig. c.

Is there anything that I can add to the code above that will do this please?

Thank you,

[v-cgao-msft · Accepted solution]

Hi @macgeorge ,

Please try:

%  Compliant = DIVIDE(SUM('Table'[Spot Checks Compliant]),SUM('Table'[Spot Checks Total]))

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly -- How to provide sample data

[macgeorge]

Yeah I can see this now. Thank you.

[macgeorge]

Apologies the tables seem to have lost its formatting - they should look like this:

Fig. a

Fig. b

Fig. c

[macgeorge]

Hello Ibendin, 

This is the 'Outcomes' table (Fig. a) I am using.

Fig. a

Priority	Spot Checks Totals	Spot Checks Compliant	% Compliant
PRODUCT 1	2	0	0%
PRODUCT 1	5	3	60%
PRODUCT 1	3	3	100%
PRODUCT 2	8	0	0%
PRODUCT 4	240	230	96%
PRODUCT 3	156	151	97%
PRODUCT 3	177	172	97%
PRODUCT 3	21	21	100%
PRODUCT 5	46	24	52%

When I add a new column and use the DAX code below I get this table (Fig. b).

% Compliant = ('Outcomes'[Spot Checks Compliant]/[Spot Checks Totals])

Fig. b 

Priority	Spot Checks Totals	Spot Checks Compliant	% Compliant
PRODUCT 1	10	6	160%
PRODUCT 2	8	0	0%
PRODUCT 4	240	230	192%
PRODUCT 3	354	344	494%
PRODUCT 5	46	24	104%

The calculations in Fig. b for Spot Checks Totals and Spot Checks Compliant columns are correct. For example the code divides Spot Checks Compliant "6", with Spot Checks Totals "10" presents the % Compliant total (referencing Fig. A) incorrectly, when it should present 60% and not 160%.

Fig. c shows how I want the data to be presented in the required table below.

Fig. c

Priority	Spot Checks Totals	Spot Checks Compliant	% Compliant
PRODUCT 1	10	6	60%
PRODUCT 2	8	0	0%
PRODUCT 4	240	230	96%
PRODUCT 3	354	344	97%
PRODUCT 5	46	24	52%

Is there anyway to use the totals as above in Fig. b and to produce the % on that table totals.

Does this make sense?

Thank you,

[lbendlin]

Please provide sanitized sample data that fully covers your issue.
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...
Please show the expected outcome based on the sample data you provided.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

[macgeorge]

Sorry I misspelled your name Ibendlin.