Problem with total on a measure

[Anonymous]

Hi, i have been working with the next data model and have found a few troubles working with measures to show the total:

Data Model

mi calendar is this and i manage it with "accounting period"

Periods table

and finally i have the table with the measures, the first and the second one are a simple sum of rev column (rev table) named "[rev uf]" and collect (collect table) named "[collect uf]"and this two are the only ones that come from this two tables.

Measures

then i have all my measures:

- CxC_i = [Rev UF]-[Collect UF]

- CxC_t = if(CALCULATE([CxC_i],FILTER(all(Periodos),Periodos[Accounting Period]<=SELECTEDVALUE(Periodos[Accounting Period])))<0.1,0,CALCULATE([CxC_i],FILTER(all(Periodos),Periodos[Accounting Period]<=SELECTEDVALUE(Periodos[Accounting Period]))))

-CxC_i_Signo = SUMX('Project Financial Billings',if([CxC_t]>0,if('Project Financial Revenue'[Rev UF]>0,'Project Financial Revenue'[Rev UF],0)+if([Collect UF]<0,[Collect UF]*-1,0),if('Project Financial Revenue'[Rev UF]<0,'Project Financial Revenue'[Rev UF],0)+if([Collect UF]>0,[Collect UF],0)))

- CxC_i_Distinto = SUMX('Project Financial Billings',if([CxC_t]>0,if('Project Financial Revenue'[Rev UF]<=0,'Project Financial Revenue'[Rev UF],0)+if([Collect UF]>0,[Collect UF]*-1,0),if([Rev UF]>0,'Project Financial Revenue'[Rev UF],0)+if([Collect UF]<=0,[Collect UF]*-1,0)))

- Days = if(DATEDIFF(max('Project Financial Billings'[collect_fechames]),date(2020,4,1),DAY)=0,1,DATEDIFF(max('Project Financial Billings'[collect_fechames]),date(2020,4,1),DAY))

- SUMA_FIFO = IF(ABS(CALCULATE([CxC_i_Distinto],ALL(Periodos[Accounting Period])))>ABS(CALCULATE([CxC_i_Signo],FILTER(ALL(Periodos[Accounting Period]),Periodos[Accounting Period]<=SELECTEDVALUE(Periodos[Accounting Period])))),0,CALCULATE([CxC_i_Distinto],ALL(Periodos[Accounting Period]))+CALCULATE([CxC_i_Signo],FILTER(ALL(Periodos[Accounting Period]),Periodos[Accounting Period]<=SELECTEDVALUE(Periodos[Accounting Period]))))

- DSO_Days = if (HASONEFILTER(Periodos[Accounting Period]),ROUND(IFERROR(if([SUMA_FIFO]=0,0,([SUMA_FIFO]-[fifo_anterior])*[Days]/[CxC_t]*SIGN([CxC_t])),1),0),CALCULATE(ROUND(IFERROR(if([SUMA_FIFO]=0,0,([SUMA_FIFO]-[fifo_anterior])*[Days]/[CxC_t]*SIGN([CxC_t])),1),0),FILTER(all(Periodos[Accounting Period]),Periodos[Accounting Period]<=SELECTEDVALUE(Periodos[Accounting Period]))))

Finally my problem is that my final measure [DSO Days] does not show the sum in the total section, in the project 113 should return 31 and in the project 56 should return 8, i have tried with calculate to unfilter the periods table but still not working.

Thank you for your time!! 

[MFelix · Accepted solution]

Hi @Anonymous ,

Measures are based on context so they are always calculated based on the filters, columns, slicers and so on that you have on your visualizations. In this case this type of calculation needs to have an aggregator measure you need to do something similar to this:

DSO TOTAL = IF(HASONEVALUE(Periodos[Accounting Period]), [DSO_Days], SUMX(Periodos,[DSO_Days]))

Then use this measure in the matrix. be aware that the table names and columns may needs changes.