Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! 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)
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 )
Proud to be a Datanaut!
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 )
Proud to be a Datanaut!
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
not at all, go for it
glad to help
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
41 | |
19 | |
19 | |
16 | |
15 |
User | Count |
---|---|
49 | |
26 | |
22 | |
17 | |
16 |