How to calculate percentage in Power BI with multiple criteria just like pivot table

[Anonymous]

Hi guys,

I have a question how to formulate the measure in power BI to create the percentage in visual chart form.

the below criteria is needed:

1. rejection quantity (TEXT column) divided by input quantity (TEXT column) to get the percentage
2. when I filter a certain month, the rejection and input will only show the selected months
3. I think there will be 3 measures need to be formulated to show the result below (line chart)

Expected Result:

- When I selected a range of month, A column chart show part no vs quantity, rejection rate as line, second axis

- When I further selected a part no, the B column chart show month vs quantity, rejection rate as line, (measure 2).

- There is also a supplier vs rejection C column chart, rejection rate as line (measure 3).

- In all condition, the rejection rate remain unchanged if the month filter remain the same

DATE	PART NUMBER	QTY	TO LOCATION	CODE SUPPLIER	TEXT	SERIES
31/3/2023	5086	76	XRE1	SL	SHRINKAGE	G
27/3/2023	4597	1	XRE1	W	SHRINKAGE	R
15/3/2023	5164	25	XRE1	Z	SHRINKAGE	G
13/3/2023	1564	6	XRE1	P	SHRINKAGE	R
02/3/2023	1564	5	XRE1	P	P.HOLE	K
24/4/2023	4673	7	XRE1	P	P.HOLE	j
15/4/2023	7634	7	XRE1	P	P.HOLE	R
31/4/2023	4673	9	XRE1	P	P.HOLE	R
1/4/2023	4597	81	XRE1	P	SHRINKAGE	G
8/4/2023	1564	2	XRE1	Y	P.HOLE	G
1/3/2023	1564	9000		Y	INPUT	G
1/3/2023	5086	5604		P	INPUT	R
1/4/2023	4597	6599		P	INPUT	R

[devanshi]

abc=

VAR selected=SELECTEDVALUE(Month)

Filter( TableName,Divide(SUM(Part),SUM(Quantity),

TableName,Divide(Month(Date),SUM(Quantity)))

RETURN

   COUNT(Supplier),RejectionRate

[v-jingzhang]

Hi @Anonymous 

You may try a measure like below for rejection rate. Only number type values can do DIVIDE and SUM operations, so ensure to change two quantity columns into whole number or decimal number type in advance. 

Rejection rate = DIVIDE(SUM('Table'[Rejection quantity]),SUM('Table'[Input quantity]))

With this condition "- In all condition, the rejection rate remain unchanged if the month filter remain the same", you can try this measure for rejection rate. Only the filter on [Month] column will be kept in the calculation. 

Rejection rate =
DIVIDE (
    CALCULATE (
        SUM ( 'Table'[Rejection quantity] ),
        ALLEXCEPT ( 'Table', 'Table'[Month] )
    ),
    CALCULATE (
        SUM ( 'Table'[Input quantity] ),
        ALLEXCEPT ( 'Table', 'Table'[Month] )
    )
)

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[Anonymous]

Thank you for the reply but seem like you already misunderstanding the question. I am a newbie in Power BI (just 5 days new). If you don’t mine, would you help to elaborate the method I should use?

Let me further simplify the question.

Let say I have 2 table below.

DATE	PART NUMBER	QTY	CODE SUPPLIER	TEXT
31/3/2023	A	76	SL	SHRINKAGE
27/3/2023	B	1	W	SHRINKAGE
15/3/2023	B	25	Z	SHRINKAGE
13/3/2023	A	6	P	SHRINKAGE
02/4/2023	C	5	P	P.HOLE
24/4/2023	B	7	P	P.HOLE
15/4/2023	C	7	P	P.HOLE
31/4/2023	B	9	P	P.HOLE

DATE	PART NUMBER	QTY	CODE SUPPLIER	TEXT
1/3/2023	A	1000	P	INPUT
1/3/2023	A	1000	P	INPUT
8/3/2023	B	2000	Y	INPUT
1/4/2023	A	9000	Y	INPUT
1/4/2023	B	5000	P	INPUT
1/4/2023	C	6000	P	INPUT

How can I link these 2 tables (need to use relationship?) so that I can use the below measure to make it work perfectly.

Rejection rate = DIVIDE(SUM('Table 1'[QTY]), SUM('Table 2'[QTY]))

When I filter certain period, the chart will show the rejection rate of that period but not always divide all the input in table 2