Divide measures, total line ignore 0s in one

[Anonymous]

I have two measures in a table containing numbers.

Measures A and B.

A	B
5	2
8	5
10	0
0	5

I want to divide B with A and get percentages, like so:

A	B	Measure
5	2	40 %
8	5	63 %
10	0
0	5	0 %

But for the total line, I want the sum of B divided by sum of A, ONLY if column A has a number, like so:

	A	B	Measure
	5	2	40 %
	8	5	63 %
	10	0
	0	5	0 %
Total	23	12	30 %

The number 5 in the last B cell is not included in the total.
The calculation becomes (12-5) / 23 = 30 %

[v-juanli-msft]

Hi @Anonymous 

Measure = CALCULATE(SUM(Sheet1[B]),FILTER(Sheet1,Sheet1[A]<>BLANK()||Sheet1[A]<>0))/SUM(Sheet1[A])

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thanks, but SUM does not work on measures. Any other suggestions?

[v-juanli-msft · Accepted solution]

Hello @Preben

Measure = SUMX(FILTER(Sheet1,Sheet1[A]<>BLANK()|| Sheet1[A]<>0),Sheet1[B])/SUMX(Sheet1,Sheet1[A])

Best regards

Maggie

[amitchandak]

@Anonymous , Use divide function

[Anonymous]

No. A simple divide gives the example I gave in the thread:

A	B	Measure
5	2	40 %
8	5	63 %
10	0
0	5	0 %

= 52 %

It should ignore the last B column because the A column has a 0. 

This rule should only work on A, and not B. 

The correct answer should give 30 %