Calculating a measure with a virtual table
Dear all, I need to know the total produced for each "aplicação" filtered by a date (unique value) from another table using a virtual table. I've made the two measures below. The firts one brings me the correct result for the total and can be filtered in the visual but it's not related to the "Aplicação". The second one have a relationship but it brings the total values without filters. Novo Prog (pç) = CALCULATE( SUM('fPosDiária_SRQ'[Prod.Peça]),FILTER(ALLSELECTED('fPosDiária_SRQ'),'fPosDiária_SRQ'[TURN_DATA_LOGICA]<SELECTEDVALUE('PD_Base PwBi'[dt_base])),VALUES(dAplicacao[Aplicação])) Measure = SUMX(CALCULATETABLE(SUMMARIZE('fPosDiária_SRQ','fPosDiária_SRQ'[Aplicação],'fPosDiária_SRQ'[TURN_DATA_LOGICA] ,"Qtd", SUM('fPosDiária_SRQ'[Prod.Peça])),'Table 2'[TURN_DATA_LOGICA] < SELECTEDVALUE('PD_Base PwBi'[dt_base]) && 'Table 2'[TURN_DATA_LOGICA] > SELECTEDVALUE('PM Dia_Base PwBi'[dt_base]), TREATAS('Table 2',dAplicacao[Aplicação], 'fPosDiária_SRQ'[Prod.Peça] ,dCalendarioLiberacao[Data])),SUM('Table 2'[Qtd])) Need help to understand why the measures above aren't working. I know the calculate/calculatetable "erases" the relationships. Can I make this calculation or do I need to use a physical table to find this value. I don't know if I was clear, but appreciate some help. Thanks, Ricardo
SUMX for Rolling Forecast with Two FactTables (Actual x Forecast)
Hi guys, I have two separeted Fact Tables (Actual and Forecast) linked in a Calendar Dimension Table. In the Forecast Table I have data of only Month of the "Year to go" (Example: Jun to Dez). Besides, in the Actual table I have only data of the YTD Months (example: Jan to May). So I need to make an weighted average using the two fact tables, filtering the lastdate with data of the Actual in the Forecast Table and interate over this virtual DAX table of Actual and Forecasting. I know that I could embend the two tables in power query. But as a matter of learning and making the worksheet more smooth. I would like to try this one. Here is my DataSet and my last Dax Measure my last result, the proble is in the subtotal over the iteration issue of sumx Thanks in !!! Data help and tips @facttables fact table and dimension table
