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.
Go to 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)
VAR __PerMonth =
SUMMARIZE ( 'Calendar', 'Calendar'[Month] ),
VAR __NoBlanks =
FILTER ( __PerMonth, [GCTonnes] <> BLANK () && [MRTonnes] <> BLANK () )
VAR __NoBlanksGC =
SUMX ( __NoBlanks, [GCTonnes] )
VAR __NoBlanksMR =
SUMX ( __NoBlanks, [MRTonnes] )
DIVIDE ( __NoBlanksGC, __NoBlanksMR )
Proud to be a Datanaut!
Mate, you're a legend! Thank you - you have made my Monday and it works!
glad to help
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 ?
not at all, go for it
Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.
We spoke with Power BI Super User, Greg Deckler, about his charity work
Continue your learning in our online communities.