Perform the equivalent of Excel's SUMPRODUCT on an average * Column , DIVIDED by a other Column

[Anonymous]

Hello, I have been days trying to solve this issue and I really seem to be at my wit's end, maybe because I'm still too noob. I have this tables and columns of data: 

Table1:

COLUMN_A = Decimal Number 

COLUMN_B= String, can be two categories, A or B 

DATEKEY =Date

Table2: datetable (with all the usual date table stuff, plus two extra collumns):

Usual date table stuff: each row is a day of the year. 

Date= is the datekey of the datetable,  for example 12/02/2020, 

Year = example, 2020 if datekey is 12/02/2020

Mes = Month of datekey in integer, january = 1, for example, for 12/02/2020 it would be 2. 

ETC (doesn't matter)

Not so usual datetable stuff:

-Number_of_days_of_month =  Integer, number of days of that month for that row (each row is a day in a year, for example, if datekey is12/02/2020 ;Number_of_days_of_month would have the number 29, as 29 days) 

-SUMDaynumsofmonth = Integer, agregation of all the total days of all the months that have ocurred up to that point for that row (each row, as said , is a day in a year, so for example if datekey is 12/02/2020, SUMDaynumsofmonth would have the value of 60, because january had 31 days, and february has 29 days, 31 + 29 = 60, this column always gives the sumation of all days of all the months up to that point, NOT the days of the year up to that point, other examples: for datekey 27/04/2020  it would return 31(january) + 29(february) + 31(march) + 30(april) = 121, all rows with datekey of the month of april would return 121 , datekey 29/02/2020 would return 60 again, all the rows in datekey of the month of february would return 60,  essentially, it only cares about the month of the datekey, when performing the calculation, not the day.   

For this column a dax formula was used (that works fine from what I can see, I will share it's calculation nevertheless for completeness of the question) :

SUMDaynumsofmonths = 
//first we calculate the number of days of each month//
VAR days_january = DAY(EOMONTH(DATE( YEAR(datetable[Date]),1,1), 0)) 

VAR days_february = DAY(EOMONTH(DATE( YEAR(datetable[Date]),2,1), 0))

VAR days_march = DAY(EOMONTH(DATE( YEAR(datetable[Date]),3,1), 0))

VAR days_april = DAY(EOMONTH(DATE( YEAR(datetable[Date]),4,1), 0))

VAR days_may = DAY(EOMONTH(DATE( YEAR(datetable[Date]),5,1), 0))

VAR days_june = DAY(EOMONTH(DATE( YEAR(datetable[Date]),6,1), 0))

VAR days_july = DAY(EOMONTH(DATE( YEAR(datetable[Date]),7,1), 0))

VAR days_august = DAY(EOMONTH(DATE( YEAR(datetable[Date]),8,1), 0))

VAR days_september = DAY(EOMONTH(DATE( YEAR(datetable[Date]),9,1), 0))

VAR days_october = DAY(EOMONTH(DATE( YEAR(datetable[Date]),10,1), 0))

VAR days_november = DAY(EOMONTH(DATE( YEAR(datetable[Date]),11,1), 0))

VAR days_december = DAY(EOMONTH(DATE( YEAR(datetable[Date]),12,1), 0))

RETURN
//now, depending of the month of the datekey, defined by column mes, we will add the corresponding variables to include the total days of only
the months up to the one we are receiving on mes column //
IF(datetable[Mes] = 1 ,days_january ,

 IF (datetable[Mes] = 2 , (days_january + days_february) , 

 IF (datetable[Mes] = 3 , (days_january + days_february + days_march) ,

 IF (datetable[Mes] = 4 , (days_january + days_february + days_march + days_april), 

 IF (datetable[Mes] = 5 , (days_january + days_february + days_march + days_april + days_may) ,

 IF (datetable[Mes] = 6 , (days_january + days_february + days_march + days_april + days_may + days_june),

 IF (datetable[Mes] = 7 , (days_january + days_february + days_march + days_april + days_may + days_june + days_july),

 IF (datetable[Mes] = 8 , (days_january + days_february + days_march + days_april + days_may + days_june + days_july + days_august),

 IF (datetable[Mes] = 9 , (days_january + days_february + days_march + days_april + days_may + days_june + days_july + days_august + days_september),

 IF (datetable[Mes] = 10 , (days_january + days_february + days_march + days_april + days_may + days_june + days_july + days_august + days_september + days_october), 

 IF (datetable[Mes] = 11 , (days_january + days_february + days_march + days_april + days_may + days_june + days_july + days_august + days_september + days_october + days_november),

 IF (datetable[Mes] = 12 , (days_january + days_february + days_march + days_april + days_may + days_june + days_july + days_august + days_september + days_october + days_november + days_december))

 )

 )

 )

 )

 )

 )

 )

 )

 )

 )

 )

I repeat, this column works fine for the moment, the problem comes later

Metrics to calculate

-metricA =  Basically gets the average of COLUMN_A by a specific category , dax formula used:

  metricA = CALCULATE(AVERAGE(Table1[COLUMN_A ]) , Table1[COLUMN_B] = "A")

This metric works fine too, for the moment. 

-MetricB: Now is where we get to the problem, this metric, should do two things: 

1) for each month, calculate metricA (that  is essentially, an average with a filter) it should get the average of only that month's data, and afterwards multiply it by the Number_of_days_of_month. It should do this in it's own context for each month. 

2) Depending of the month we are working on the graph ( we are creating a matrix visual, that should represent the data like this:)

It will add all the metricA of each month up to that point,  and then divide them by the corresponding SUMDaynumsofmonth , each month in it's own context. Each number that we see in the row, is basically the 

SUM(metricA's of all months up to that point) / SUMDaynumsofmonth ( Essentially the sum of all the days that the months up to that point, have). 

So how did I go about this? 

MetricB = // Part 1)first we specify that we want metricA, and we calculate it on it's own context as a variable, for each month//
VAR presupuesto_a_calcular = [metricA]

VAR Pres_january = (CALCULATE(presupuesto_a_calcular , datetable[Mes] = 1) * CALCULATE(MAX(datetable[NºDaysinMonth]) , datetable[Mes]= 1))

VAR Pres_february = ((CALCULATE(presupuesto_a_calcular , datetable[Mes] = 2) * CALCULATE(MAX(datetable[NºDaysinMonth]) , datetable[Mes] = 2)))

VAR Pres_march = (CALCULATE(presupuesto_a_calcular , datetable[Mes] = 3) * CALCULATE(MAX(datetable[NºDaysinMonth]) , datetable[Mes] = 3))

VAR Pres_april = (CALCULATE(presupuesto_a_calcular , datetable[Mes] = 4) * CALCULATE(MAX(datetable[NºDaysinMonth]) , datetable[Mes] = 4))

VAR Pres_may = (CALCULATE(presupuesto_a_calcular , datetable[Mes] = 5) * CALCULATE(MAX(datetable[NºDaysinMonth]) , datetable[Mes] = 5))

VAR Pres_june = (CALCULATE(presupuesto_a_calcular , datetable[Mes] = 6) * CALCULATE(MAX(datetable[NºDaysinMonth]) , datetable[Mes] = 6))

VAR Pres_july = (CALCULATE(presupuesto_a_calcular , datetable[Mes] = 7) * CALCULATE(MAX(datetable[NºDaysinMonth]) , datetable[Mes] = 7))

VAR Pres_august = (CALCULATE(presupuesto_a_calcular , datetable[Mes] = 8 * CALCULATE(MAX(datetable[NºDaysinMonth]) , datetable[Mes] = 8))

VAR Pres_september = (CALCULATE(presupuesto_a_calcular , datetable[Mes] = 9) * CALCULATE(MAX(datetable[NºDaysinMonth]) , datetable[Mes] = 9))

VAR Pres_october = (CALCULATE(presupuesto_a_calcular , datetable[Mes] = 10) * CALCULATE(MAX(datetable[NºDaysinMonth]) , datetable[Mes] = 10))

VAR Pres_november = (CALCULATE(presupuesto_a_calcular , datetable[Mes] = 11) * CALCULATE(MAX(datetable[NºDaysinMonth]) , datetable[Mes] = 11))

VAR Pres_december = (CALCULATE(presupuesto_a_calcular , datetable[Mes] = 12) * CALCULATE(MAX(datetable[NºDaysinMonth]) , datetable[Mes] = 12))

RETURN
//Part2) If statements that make it so, that depending of what month we are in, we will add our precalculated variables created earlier, and
divide it by the corresponding SUMDaynumsofmonths of that month)//
IF(MAX(datetable[Mes]) = 1 ,DIVIDE(Pres_january ,CALCULATE(MAX(datetable[SUMDaynumsofmonths]) , datetable[Mes] = 1)),

 IF(MAX(datetable[Mes]) = 2 , DIVIDE((Pres_january + Pres_february) , CALCULATE(MAX(datetable[SUMDaynumsofmonths]) , datetable[Mes] = 2)),

 IF(MAX(datetable[Mes]) = 3 , DIVIDE((Pres_january + Pres_february + Pres_march) ,CALCULATE(MAX(datetable[SUMDaynumsofmonths]) , datetable[Mes] = 3)) ,

 IF(MAX(datetable[Mes]) = 4 , DIVIDE((Pres_january + Pres_february + Pres_march + Pres_april) ,CALCULATE(MAX(datetable[SUMDaynumsofmonths]) , datetable[Mes] = 4)), 

 IF(MAX(datetable[Mes]) = 5 , DIVIDE((Pres_january + Pres_february + Pres_march + Pres_april + Pres_may) ,CALCULATE(MAX(datetable[SUMDaynumsofmonths]) , datetable[Mes] = 5)),

 IF(MAX(datetable[Mes]) = 6 , DIVIDE((Pres_january + Pres_february + Pres_march + Pres_april +Pres_may + Pres_june) ,CALCULATE(MAX(datetable[SUMDaynumsofmonths]) , datetable[Mes] = 6)) ,

 IF(MAX(datetable[Mes]) = 7 , DIVIDE((Pres_january + Pres_february + Pres_march + Pres_april +Pres_may + Pres_june + Pres_july) ,CALCULATE(MAX(datetable[SUMDaynumsofmonths]) , datetable[Mes] = 7)),

 IF(MAX(datetable[Mes]) = 8 , DIVIDE((Pres_january + Pres_february + Pres_march + Pres_april +Pres_may + Pres_june + Pres_july + Pres_august) ,CALCULATE(MAX(datetable[SUMDaynumsofmonths]) , datetable[Mes] = 8)),

 IF(MAX(datetable[Mes]) = 9 , DIVIDE((Pres_january + Pres_february + Pres_march + Pres_april +Pres_may + Pres_june + Pres_july + Pres_august + Pres_september) ,CALCULATE(MAX(datetable[SUMDaynumsofmonths]) , datetable[Mes] = 9)),

 IF(MAX(datetable[Mes]) = 10 , DIVIDE((Pres_january + Pres_february + Pres_march + Pres_april +Pres_may + Pres_june + Pres_july + Pres_august + Pres_september + Pres_october) ,CALCULATE(MAX(datetable[SUMDaynumsofmonths]) , datetable[Mes] = 10)),

IF(MAX(datetable[Mes]) = 11 , DIVIDE((Pres_january + Pres_february + Pres_march + Pres_april +Pres_may + Pres_june + Pres_july + Pres_august + Pres_september + Pres_october + Pres_november) ,CALCULATE(MAX(datetable[SUMDaynumsofmonths]) , datetable[Mes] = 11)),

IF(MAX(datetable[Mes]) = 12 , DIVIDE((Pres_january + Pres_february + Pres_march + Pres_april +Pres_may + Pres_june + Pres_july + Pres_august + Pres_september + Pres_october + Pres_november + Pres_december) ,CALCULATE(MAX(datetable[SUMDaynumsofmonths]) , datetable[Mes] = 12)))

 )

 )

 )

 )

 )

 )

 )

 )

 )

 )

 )

Here is where my code fails, for some reason it doesn't work, returning a smaller division that it should. 

I Have Applied this process , and it works outside of the big metricB formula, if I do this, it works: 

januaryexample = CALCULATE(Table1[MetricA], datetable[Mes] = 1) * CALCULATE(MAX(datetable[NºDaysinMonth]) , datetable[Mes]= 1)   

februaryexample  = CALCULATE(Table1[MetricA], datetable[Mes] = 2) * CALCULATE(MAX(datetable[NºDaysinMonth]) , datetable[Mes]= 2)   

MetricBforFebruary = DIVIDE(([januaryexample] + [februaryexample]) , CALCULATE(MAX(datetable[SUMDaynumsofmonths]) , datetable[Mes] = 2))

When I break it down in three metrics like this the desired result is returned for february in MetricforFebruary, but not so if I do it for all months in MetricB, What is the reason for this? 

[technolog · Accepted solution]

You're trying to replicate the SUMPRODUCT function from Excel in Power BI using DAX. You have two tables, Table1 and Table2 (which is a date table with some additional columns). You've already created a metric called metricA which calculates the average of COLUMN_A for category "A".

Now, for MetricB, you want to do two things:

For each month, calculate metricA for that month and multiply it by the number of days in that month.
Depending on the month, sum up all the metricA values for all the months up to that point and then divide them by the SUMDaynumsofmonth for that month.
You've tried to achieve this by creating a series of VARs for each month and then using nested IF statements to calculate MetricB. However, this approach is not only lengthy but also inflexible. If you were to add more months or years, you'd have to keep updating the formula which is not ideal.

The first response you received suggests that you shouldn't create such long formulas and instead use a more efficient approach. The responder provided a formula to calculate the sum of all days up to the last day of the current month. This formula is more concise and flexible than the one you provided. It uses a variable to store the current month and then counts the number of rows in the Calendar table that are less than or equal to the current month. This gives you the total number of days up to the last day of the current month.

The second response points out that the definition of MetricB is inconsistent and can't be created as described. This suggests that there might be some logical errors in the way you've defined MetricB.

To solve your problem, I'd recommend the following:

Use the formula provided in the first response to calculate the sum of all days up to the last day of the current month. This will give you a more efficient way to calculate the SUMDaynumsofmonth.
For MetricB, instead of creating separate VARs for each month, try to use a more dynamic approach. For example, you could use the SUMX function to iterate over each month and calculate the metricA for that month. Then, you can use the result to calculate MetricB.
Re-analyze the definition of MetricB to ensure that it's logically consistent. If there are any inconsistencies, try to resolve them before implementing the formula in DAX.

[Anonymous]

Hi.

First, you should not create such formulas. They're not only overly long. They're inflexible and just not the way to do it. Please use the below to create the column:

// Calendar should start with the first day of a year
// and contain all days of all years present in
// the calendar. You should also have a column where
// the entry will be an integer of the form YYYYMM =
// Year( [Date] ) * 100 + Month( [Date] ). Name the
// column, for instance, YearMonthOrder, and hide it.
// This number uniquely identifies months and is
// the sort order for them. Then you can calculate:

SumOfAllDaysUpToLastDayOfCurrentMonth = // calc column
var __currentMonth = Calendar[YearMonthOrder]
var __total =
	countrows(
		filter(
			'Calendar',
			'Calendar'[YearMonthOrder] <= __currentMonth
		)
	)
return
	__total
	
// Your monster formula is not the way to do this
// in DAX. It's not only hard to read and maintain.

// If you want to reset the counter every year,
// then use this formulation:

SumOfAllDaysInYearUpToLastDayOfCurrentMonth = // calc column
var __currentMonth = Calendar[YearMonthOrder]
var __currentYear = Calendar[Year]
var __total =
	countrows(
		filter(
			'Calendar',
			'Calendar'[YearMonthOrder] <= __currentMonth
			&&
			'Calendar'[Year] = __currentYear
		)
	)
return
	__total

The rest will come later.

Best

D

[Anonymous]

Hi there again.

Sorry to say that but upon analysis of what you've described I can tell you that the definition of MetricB is inconsistent. It can't be created. Please re-analyze the definition and make it logically feasible.

Best
D