Table level calculation

[praveenlc]

Hi, I am stuck with achieving table level calculation, any quick help is appreciated. it could be a measure or column 

Problem is to pick the appropriate cell value and subtract it against the previous cell value

I tried few dax but no luck

total = CALCULATE(SUM('Facttable (2)'[Value]),ALLEXCEPT('Facttable (2)','Facttable (2)'[Fleet No]))

total - cellvalue = 

CALCULATE(SUM('Facttable (2)'[Value]),ALLEXCEPT('Facttable (2)','Facttable (2)'[Fleet No])) - CALCULATE(SUM('Facttable (2)'[Value]),FILTER(ALLEXCEPT('Facttable (2)','Facttable (2)'[Fleet No]),'Facttable (2)'[Index]-1))

ID	EndWeek	Value	Index	Expected Results		Calculation
11501	10/7/2018	242363	1	0		Default "0"	If Index=1 then 0
11501	11/4/2018	248711	2	6348		"=C3-C2"	248711 - 242363	Index1-Index2
11501	12/2/2018	253163	3	4452		"=C4-C3"	253163 - 248711	Index2-Index3
11501	12/31/2018	258869	4	5706		"=C5-C4"
11501	1/27/2019	264559	5	5690		"=C6-C5"
11501	2/24/2019	270151	6	5592		"=C7-C6"
23488	3/24/2019	274269	1	0		Default "0"
23488	4/21/2019	279916	2	5647		"=C9-C8"
23488	5/19/2019	283859	3	3943		"=C10-C9"
23488	6/16/2019	290555	4	6696		"=C11-C10"
23488	7/14/2019	294742	5	4187		"=C12-C11"

[selimovd · Accepted solution]

Hey @praveenlc ,

I don't see a Fleet No in your example, but the following calculated column should work for the data you posted:

myResult = 
VAR vCurrentValue = Facttable[Value]
VAR vCurrentIndex = Facttable[Index]
RETURN
    IF(
        vCurrentIndex = 1,
        0,
        vCurrentValue - CALCULATE(SUM(Facttable[Value]), Facttable[Index] = vCurrentIndex - 1 , ALLEXCEPT(Facttable, Facttable[ID]))
    )

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic

[praveenlc]

hi @selimovd thanks and appreciate your quick response

I did try VAR on index but was not aware that VAR could be used for values also.

Thanks again!

Best wishes

[selimovd]

Hey @praveenlc ,

I'm happy it works 😊

Yes, VAR is pretty dynamic, you can save the result of measures, the current column value or a row like done here and also whole tables, for example with the SUMMARIZE or VALUES function.

Best regards

Denis

[praveenlc]

ID		EndWeek	Value	Index	Expected Results		Calculation
11501		10/7/2018	242363	1	0		Default "0"	If Index=1 then 0
11501		11/4/2018	248711	2	6348		"=C3-C2"	248711 - 242363	Index1-Index2
11501		12/2/2018	253163	3	4452		"=C4-C3"	253163 - 248711	Index2-Index3
11501		12/31/2018	258869	4	5706		"=C5-C4"
11501		1/27/2019	264559	5	5690		"=C6-C5"
11501		2/24/2019	270151	6	5592		"=C7-C6"
23488		3/24/2019	274269	1	0		Default "0"
23488		4/21/2019	279916	2	5647		"=C9-C8"
23488		5/19/2019	283859	3	3943		"=C10-C9"
23488		6/16/2019	290555	4	6696		"=C11-C10"
23488		7/14/2019	294742	5	4187		"=C12-C11"

[praveenlc]

ID		EndWeek		Value		Index	Expected Results		Calculation
11501		10/7/2018		242363		1	0		Default "0"	If Index=1 then 0
11501		11/4/2018		248711		2	6348		"=C3-C2"	248711 - 242363	Index1-Index2
11501		12/2/2018		253163		3	4452		"=C4-C3"	253163 - 248711	Index2-Index3
11501		12/31/2018		258869		4	5706		"=C5-C4"
11501		1/27/2019		264559		5	5690		"=C6-C5"
11501		2/24/2019		270151		6	5592		"=C7-C6"
23488		3/24/2019		274269		1	0		Default "0"
23488		4/21/2019		279916		2	5647		"=C9-C8"
23488		5/19/2019		283859		3	3943		"=C10-C9"
23488		6/16/2019		290555		4	6696		"=C11-C10"
23488		7/14/2019		294742		5	4187		"=C12-C11"