cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

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

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:)

2020-04-30 13_12_18-INFORMES FEBRERO CINTRA_00_visualización_cintra1 output_acumulados - Excel.png

 
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? 

2 REPLIES 2
Highlighted
Anonymous
Not applicable

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

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

Highlighted
Anonymous
Not applicable

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

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

Helpful resources

Announcements
Super Users of the Quarter - Q2 2020

Super Users of the Quarter - Q2 2020

Who are our Super User Superstars? Who made it to the top of the leaderboards? Get the answers!

June 2020 Community Highlights

June 2020 Community Highlights

Featured community members, changes to the Community, and more! Read up on recent Power BI community news.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Power Platform 2020 release wave 2 plan

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors