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:) 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?Ignore context filter within summarize DAX
have a calculation where for all years I divide the value of a given year. This needs to be done at a group "sector" level. When I put the measure in a visual with the "sectors" in the legend of the visual, the calculation works fine: However, now I want a visual with the total, so not showing the values per sector. To do this I thought it would be best to use a summarize function. However now the problem is that I am not able to get rid of the year context within the summarize function. I tried to use the crossfilter function with setting it to none. So the value is now only shown for the "given year"= 2000 (for 14 sectors): Any help is much appreciated! Thanks
Respecting filter context Issues, please Help!
Hello Everyone, I think I'm having filter context issues! any help or hints would be very much appreciated! I have this table called Ranking_New_Employee Team or IA NET ranking for seniority Total Net to total Adam 329 New Employee 2502 13% Jessica 236 New Employee 879 27% Ben 181 New Employee 1105 16% Mark 164 New Employee 905 18% Dumanowski 145 New Employee 2843 5% Ewing 116 New Employee 854 14% Vooys 104 New Employee 2970 4% Donahue 82 New Employee 912 9% Mazigi 70 New Employee 451 16% Gonzalez 66 New Employee 279 24% Sherwick 63 New Employee 1649 4% Blaine 48 New Employee 1022 5% Andrew 45 New Employee 227 20% Cordel 40 New Employee 1711 2% Garry 38 New Employee 852 4% Pelleter 31 New Employee 1305 2% Catherine 30 New Employee 1008 3% Dan 27 New Employee 373 7% Reid 24 New Employee 182 13% Scott 19 New Employee 116 16% Christian 15 New Employee 1020 1% Colin 10 New Employee 236 4% Steven 3 New Employee 993 0% I have created this measure to be able to get top 10/bottom10 by Net value. TOP_New_emp = VAR raking_asc = RANKX ( ALL ( 'Ranking_New employee'[NET], 'Ranking_New employee'[Team or IA]), CALCULATE ( SUM ( 'Ranking_New employee'[NET] ) ) ) VAR ranking_desc = RANKX ( ALL ( 'Ranking_New employee'[NET], 'Ranking_New employee'[Team or IA]), CALCULATE ( SUM ( 'Ranking_New employee'[NET] ) ), ,ASC ) RETURN SWITCH ( SELECTEDVALUE ( View_New_Employee[Type] ), "Bottom 10", IF ( ranking_desc <= 10, SUM ('Ranking_New employee'[NET] ),blank()), "Top 10", IF ( raking_asc <= 10, SUM ( 'Ranking_New employee'[NET]) , blank() )) This is the result I get when I select top 10. but, of course, when I select bottom10, only the bottom 10 get selected. Team or IA Top_New_emp Adam 329 Jessica 236 Ben 181 Mark 164 Dumanowski 145 Ewing 116 Vooys 104 Donahue 82 Mazigi 70 Gonzalez 66 *****The Issue starts here***** Now, I have created this measure to get a % Net to total but when I drag this measure to the result above, I get this : Team or IA Top_New_emp Net to total Adam 329 13% Jessica 236 27% Ben 181 16% Mark 164 18% Dumanowski 145 5% Ewing 116 14% Vooys 104 4% Donahue 82 9% Mazigi 70 16% Gonzalez 66 24% Sherwick 4% Blaine 5% Andrew 20% Cordel 2% Garry 4% Pelleter 2% Catherine 3% Dan 7% Reid 13% Scott 16% Christian 1% Colin 4% Steven 0% But, this is the desired result : Team or IA Top_New_emp Net to total Adam 329 13% Jessica 236 27% Ben 181 16% Mark 164 18% Dumanowski 145 5% Ewing 116 14% Vooys 104 4% Donahue 82 9% Mazigi 70 16% Gonzalez 66 24% I would like the new calculated measure to respect Top_New_emp filter. In other words, I want to get only Net to Total values that correspond to Top_New_emp whether I select Top 10 or Bottom 10. For example, when I select Top 10, I don't want the underlined values (refer to the table above) to appear. Thanks in advance
Calculate values in one fact table from another fact table
Hi , I have following data model which has 2 fact tables 3 dimension tables and 1 bridge table to resolve many to many relationship. I am trying to calculate Sales Volume since the contract Start Date for each "Product ID" in Contracts Table using following formula Sales Volume Since Contract Start Date= CALCULATE ([Total Volume], FILTER ( SalesFact, SalesFact[INVOICE_DATE] >= MIN ( Contracts[Contract Start Date] ) ), SalesFact[PRODUCT_ID] IN VALUES ( Contracts[Product ID] ), SalesFact[BILL_TO_CUST_ID] IN VALUES ( Contracts[Bill To ID] ) ) I get correct results when I throw this measure in a table which provides Context for MIN ( Contracts[Contract Start Date] ) . However, if I throw this measure in a card visual or in any other visual without a Contract Start date context I get incorrect results. Can somone please tell me how can I resolve this? I think I need to use somekind of iterator function to get the result I need, but I can't seem to figure it out. Thanks!
