cancel
Showing results for
Did you mean:
Highlighted
Helper III

Divide measures, total line ignore 0s in one

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 %

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support

Re: Split measures, total line ignore 0s into one

Hello @Preben

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

Best regards

Maggie

5 REPLIES 5
Highlighted
Super User IV

Re: Divide measures, total line ignore 0s in one

@Preben , Use divide function

Proud to be a Super User!

Highlighted
Helper III

Re: Divide measures, total line ignore 0s in one

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 %

Highlighted
Community Support

Re: Divide measures, total line ignore 0s in one

Hi @Preben

``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.

Highlighted
Helper III

Re: Divide measures, total line ignore 0s in one

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

Re: Split measures, total line ignore 0s into one

Hello @Preben

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

Best regards

Maggie

Announcements

Power Platform Community Conference

Check out the on demand sessions that are available now!

Microsoft Power Platform Communities

Check out the Winners!

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Maratón de Soluciones de Negocio Microsoft

Una semana de contenido con +100 sesiones educativas, consultorios, +10 workshops Premium, Hackaton, EXPO, Networking Hall y mucho más!

Top Solution Authors
Top Kudoed Authors
Users online (781)