cancel
Showing results for
Did you mean:
Regular Visitor

## Ratio on total to exclude blank rows

Hi Folks

I am working out a ratio of two columns which come from seperate tables.

GC_Tonnes is the total of the tonnes in the GC Table

MR_Tonnes is the total of the tonnes in the MR Table.

Some months do not have either one or the other populated. I need the total rows ratio to be calculated when both tables have these populated for the month in question.

Here is the problem graphically.

I've tried a bunch of if statements but the total ratio still doesn't do it correctly. Any pointers would be hugely appreciated.

I have attached an example PBIX so show that there are various models available and some mmodels have months missing do to an export not having been completed.

` `

https://1drv.ms/u/s!AgXemCuZjUYGlXPrP1xdGa9sgsWn?e=FtVYeT

Cheers

Manfred

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super Contributor

## Re: Ratio on total to exclude blank rows

for the total neither the GC or MR are blank, so it just divides the values as they are

you need to first summarize per month, then filter out blanks, aggregate the result and only then divide, like this (adjust the Calendar[Month] to whatever your date dimension is)

```F1_Tonnes =
VAR __PerMonth =
SUMMARIZE ( 'Calendar', 'Calendar'[Month] ),
"GCTonnes", [GC_Tonnes],
"MRTonnes", [MR_Tonnes]
)
VAR __NoBlanks =
FILTER ( __PerMonth, [GCTonnes] <> BLANK () && [MRTonnes] <> BLANK () )
VAR __NoBlanksGC =
SUMX ( __NoBlanks, [GCTonnes] )
VAR __NoBlanksMR =
SUMX ( __NoBlanks, [MRTonnes] )
RETURN
DIVIDE ( __NoBlanksGC, __NoBlanksMR )
```

Proud to be a Datanaut!

Proud to be a Datanaut!

5 REPLIES 5
Highlighted
Super Contributor

## Re: Ratio on total to exclude blank rows

for the total neither the GC or MR are blank, so it just divides the values as they are

you need to first summarize per month, then filter out blanks, aggregate the result and only then divide, like this (adjust the Calendar[Month] to whatever your date dimension is)

```F1_Tonnes =
VAR __PerMonth =
SUMMARIZE ( 'Calendar', 'Calendar'[Month] ),
"GCTonnes", [GC_Tonnes],
"MRTonnes", [MR_Tonnes]
)
VAR __NoBlanks =
FILTER ( __PerMonth, [GCTonnes] <> BLANK () && [MRTonnes] <> BLANK () )
VAR __NoBlanksGC =
SUMX ( __NoBlanks, [GCTonnes] )
VAR __NoBlanksMR =
SUMX ( __NoBlanks, [MRTonnes] )
RETURN
DIVIDE ( __NoBlanksGC, __NoBlanksMR )
```

Proud to be a Datanaut!

Proud to be a Datanaut!

Regular Visitor

## Re: Ratio on total to exclude blank rows

Mate, you're a legend!  Thank you - you have made my Monday and it works!

Super Contributor

## Re: Ratio on total to exclude blank rows

Proud to be a Datanaut!

Regular Visitor

## Re: Ratio on total to exclude blank rows

Hi @Stachu

Your solution got me thinking about filtering the dates on my graph ( or only showing information when the graph is plotted) - I will post this on a seperate new post and tag you in it that is no problem ?

Cheers

Manfred

Super Contributor

## Re: Ratio on total to exclude blank rows

not at all, go for it

Proud to be a Datanaut!

Announcements

#### Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

#### Power BI Helps Homeless and Trouble Youth

We spoke with Power BI Super User, Greg Deckler, about his charity work

#### Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 336 members 3,758 guests
Recent signins: