Ratio on total to exclude blank rows

[mwimberger]

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

[Stachu · Accepted solution]

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!

[mwimberger]

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]

not at all, go for it

[mwimberger]

@Stachu 

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

[Stachu]

glad to help