Variation of 2 running totals

[DM_BI]

Hello,

I have 2 measures (calculated with the quick measure Running Total) : 
- Real acum (which is the running total of my sales, what really was sold)

- Objetivo acum (which is the running toal of the objective we should do)

Now I want to know the variation between these 2 measures, to see if I am reaching my goal or not. So what I did was :

Var acum % = DIVIDE(('OBJ PI'[Real acum]-'OBJ PI'[Objectivo acum]);'OBJ PI'[Objectivo acum];0)

But I get the exact same result as the variation between my Real and my Objective. I mean, what I got was that my "normal" variation equals my "cumulative" variation, which is not true.

Anyone can help me with this?

Thank you!

D.

[rafaelmpsantos]

Can you show some data example?

[DM_BI]

I can show you an exemple : in november and december my var acum (on the right) is equal is to my variation (on the left) which is not normal.  Sorry I am not allowed to share the figures 😞 I hope it's enough to understand. If not please ask me more info.

Thank you for your help

DM

[rafaelmpsantos]

"Objectivo Acumulado" is wrong to? is showing the same value.

[DM_BI]

No it's normal because in our case November is the first month of the year so you cant' see the acum for November. But for december it's ok because it's the sum of november+december.

[rafaelmpsantos]

Maybe the problems are the two measures created in quick measures. CAn you paste it here?

[DM_BI]

Yes they are created in quick measures : 

Real acum =
CALCULATE(
SUM('OBJ PI'[Real]);
FILTER(
CALCULATETABLE(
SUMMARIZE('OBJ PI'; 'OBJ PI'[num_mes_inter]; 'OBJ PI'[MES INTER]);
ALLSELECTED('OBJ PI')
);
ISONORAFTER(
'OBJ PI'[num_mes_inter]; MAX('OBJ PI'[num_mes_inter]); DESC;
'OBJ PI'[MES INTER]; MAX('OBJ PI'[MES INTER]); DESC
)
)
)

Objectivo acum =
CALCULATE(
SUM('OBJ PI'[Objetivo PI]);
FILTER(
CALCULATETABLE(
SUMMARIZE('OBJ PI'; 'OBJ PI'[num_mes_inter]; 'OBJ PI'[MES INTER]);
ALLSELECTED('OBJ PI')
);
ISONORAFTER(
'OBJ PI'[num_mes_inter]; MAX('OBJ PI'[num_mes_inter]); DESC;
'OBJ PI'[MES INTER]; MAX('OBJ PI'[MES INTER]); DESC
)
)
)

[rafaelmpsantos]

try it

Real acum =
sumx(
FILTER(
CALCULATETABLE(
SUMMARIZE('OBJ PI'; 'OBJ PI'[num_mes_inter]; 'OBJ PI'[MES INTER]);
ALLSELECTED('OBJ PI')
);
ISONORAFTER(
'OBJ PI'[num_mes_inter]; MAX('OBJ PI'[num_mes_inter]); DESC;
'OBJ PI'[MES INTER]; MAX('OBJ PI'[MES INTER]); DESC
)
);
SUM('OBJ PI'[Real])
)

Objectivo acum =
SUMX(
FILTER(
CALCULATETABLE(
SUMMARIZE('OBJ PI'; 'OBJ PI'[num_mes_inter]; 'OBJ PI'[MES INTER]);
ALLSELECTED('OBJ PI')
);
ISONORAFTER(
'OBJ PI'[num_mes_inter]; MAX('OBJ PI'[num_mes_inter]); DESC;
'OBJ PI'[MES INTER]; MAX('OBJ PI'[MES INTER]); DESC
)
);SUM('OBJ PI'[Objetivo PI])
)

[DM_BI]

Thank you, I tried with your formula but still have the same "var acum%" (same as on the left)

[v-shex-msft]

HI @DM_BI,

Can you please share a pbix file with part of sample data for test? It is hard to testing and coding formula without any sample data.

Regards,

Xiaoxin Sheng

[DM_BI]

Hi!

Of course, I made a sample. You will see that the variation is ok but the variation of running totals is not (should be -16,66%). I don't know why. In my team we need to know for example if in january+february we have achieved the objective of january+february or not.

The only problem is that I don't see any icon to insert a file here, do you know how to upload it?

Thank you for your help!!

DM

[v-shex-msft]

HI @DM_BI,

You can upload sample file to onedrive or google drive and share link here.

Regards,

Xiaoxin Sheng

[DM_BI]

Hello,

Thank you, here is the link : https://gplaneta-my.sharepoint.com/:u:/g/personal/udm06a_psoplaneta_com/EW3U057hnVpBln4S58FMj3IBVaWM...

Best regards

[v-shex-msft · Accepted solution]

Hi @DM_BI,

After play with you sample, I found you can remove column defined in allselected function to get correct result value:

FACTURATION running total in MONTH = 
CALCULATE(
	SUM('Hoja1'[FACTURATION]),
	FILTER(
		ALLSELECTED('Hoja1'),
		ISONORAFTER('Hoja1'[MONTH], MAX('Hoja1'[MONTH]), DESC)
	)
)


OBJECTIVE FACTURATION running total in MONTH = 
CALCULATE(
	SUM('Hoja1'[OBJECTIVE FACTURATION]),
	FILTER(
		ALLSELECTED('Hoja1'),
		ISONORAFTER('Hoja1'[MONTH], MAX('Hoja1'[MONTH]), DESC)
	)
)

BTW, you can't create dynamic calculated column based on slicer/filter or dynamic measure.(when you use measure in calculate column measure result will been fix)

Calculated Column/Table Change Dynamically According to Slicer Selection in the Report.

Regards,
Xiaoxin Sheng