cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper III
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
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

View solution in original post

5 REPLIES 5
Highlighted
Super User IV
Super User IV

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

@Preben , Use divide function



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Highlighted
Helper III
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
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])

Capture7.JPG

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

View solution in original post

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Tech Marathon

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)