cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mwimberger Regular Visitor
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.

2019-07-29_9-47-54.jpg

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
Stachu Super Contributor
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 =
    ADDCOLUMNS (
        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 )
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

5 REPLIES 5
Highlighted
Stachu Super Contributor
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 =
    ADDCOLUMNS (
        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 )
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

mwimberger Regular Visitor
Regular Visitor

Re: Ratio on total to exclude blank rows

@Stachu 

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

 

2019-07-29_11-25-12.jpg

Stachu Super Contributor
Super Contributor

Re: Ratio on total to exclude blank rows

glad to help Smiley Happy

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

mwimberger Regular Visitor
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

Stachu Super Contributor
Super Contributor

Re: Ratio on total to exclude blank rows

not at all, go for it

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Helpful resources

Announcements
Virtual Launch Event

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

Power BI Helps Homeless and Trouble Youth

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

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 336 members 3,758 guests
Please welcome our newest community members: