Incorrect Cumulative Total / Running Total - Text field

[ansar]

Hi Dears, Need your help here

Am trying to create a measure to get cumulative total of a [Prov USD] (Measure summing provision amounts) based on customer name. Totals showing for other two column in the below visualization are correct except [Cumulative Prov 2].

Appreciate your support here to fix it.

Cumulative Prov 2 =

VAR CrNameProv = [Prov USD]

RETURN

SUMX(

FILTER(

SUMMARIZE(ALLSELECTED(Data),'Customer DB'[Cr. Name],

"Prov Amt", [Prov USD]),

[Prov Amt] >= CrNameProv),

[Prov Amt])

Prov USD RemoveCust Filter = CALCULATE([Prov USD],ALLSELECTED('Customer DB'))

Regards,

Ansar.

[Greg_Deckler]

@ansar This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

If not, Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

[ansar]

@Greg_Deckler , i have gone through the link you have shared but not able to fix my issue,

Sample data with out put is below, 

Output

Cr. Name	Type	Pstng Date	LCurr	Agreement #	Agmt Type	Amt
Union Coop	BF	31/01/20	AED	11339	BDA	(706,603.70)
Union Coop	BF	29/02/20	AED	11339	BDA	706,603.70
Union Coop	BF	29/02/20	AED	11339	BDA	(536,966.65)
Union Coop	BF	31/03/20	AED	11339	BDA	536,966.65
Union Coop	BF	31/03/20	AED	11339	BDA	(1,157,831.71)
Union Coop	RV	12/03/20	AED	11339	BDA	216,362.47
Union Coop	RV	26/03/20	AED	11339	BDA	320,604.18
Union Coop	BF	30/04/20	AED	11339	BDA	1,157,831.71
Union Coop	BF	30/04/20	AED	11339	BDA	(2,065,185.54)
Union Coop	RV	12/04/20	AED	11339	BDA	620,865.06
Union Coop	BF	31/05/20	AED	11339	BDA	2,065,185.54
Union Coop	BF	31/05/20	AED	11339	BDA	(2,435,021.28)
Union Coop	RV	16/05/20	AED	11339	BDA	453,676.92
Union Coop	BF	30/06/20	AED	11339	BDA	2,435,021.28
Union Coop	BF	30/06/20	AED	11339	BDA	(2,530,466.04)
Union Coop	RV	05/06/20	AED	11339	BDA	145,287.86
Union Coop	RV	14/06/20	AED	11339	BDA	339,112.40
Union Coop	RV	17/06/20	AED	11339	BDA	339,112.39
Union Coop	BF	31/07/20	AED	11339	BDA	2,530,466.04
Union Coop	BF	31/07/20	AED	11339	BDA	(2,631,583.72)
Union Coop	RV	16/07/20	AED	11339	BDA	95,444.76
Union Coop	BF	31/08/20	AED	11339	BDA	2,631,583.72
Union Coop	BF	31/08/20	AED	11339	BDA	(2,943,629.67)
Union Coop	RV	09/08/20	AED	11339	BDA	101,117.68
Union Coop	BF	30/09/20	AED	11339	BDA	2,943,629.67
Union Coop	BF	30/09/20	AED	11339	BDA	(3,116,977.41)
Union Coop	RV	09/09/20	AED	11339	BDA	312,045.95
Union Coop	BF	31/10/20	AED	11339	BDA	3,116,977.41
Union Coop	BF	31/10/20	AED	11339	BDA	(3,115,166.70)
Union Coop	RV	31/10/20	AED	11339	BDA	170,000.00
Union Coop	BF	30/11/20	AED	11339	BDA	3,115,166.70
Union Coop	BF	30/11/20	AED	11339	BDA	(3,358,620.84)
Union Coop	BF	31/12/20	AED	11339	BDA	3,358,620.84
Union Coop	BF	31/12/20	AED	11339	BDA	(3,588,053.31)
Union Coop	RV	14/12/20	AED	11339	BDA	240,000.00
Union Coop	RV	27/01/21	AED	11339	BDA	4,000.00
Union Coop	RV	07/02/21	AED	11339	BDA	230,423.64
Carrefour	BF	30/11/20	SAR	18927	Punctual	(15,000.00)
Carrefour	RV	31/12/20	SAR	18927	Punctual	15,000.00
Carrefour	RV	31/07/21	SAR	18927	Punctual	(15,000.00)
Carrefour	RV	31/07/21	SAR	18927	Punctual	15,000.00
Tesco	BF	30/04/21	SAR	24426	Sell Out	(20,448.60)
Tesco	BF	26/05/21	SAR	24426	Sell Out	20,448.60
Tesco	BF	26/05/21	SAR	24426	Sell Out	(17,623.48)
Tesco	RV	30/06/21	SAR	24426	Sell Out	13,098.00
Tesco	RV	31/07/21	SAR	24426	Sell Out	4,525.48

Regards,

Ansar.

[Greg_Deckler]

@ansar So what is not correct in your image?

[ansar · Accepted solution]

@Greg_Deckler : In column 1(Cumulative Prov 2) is aggregating values from column 2(Prov USD) .The total showing in column 2 is not matching with the Column 1 where i have highlited in the image, 

It got resolved once i removed the blanks

Cumulative Value Prov =

VAR thisCustProv = [Prov USD]

VAR TotalProvAllCust =

CALCULATE([Prov USD], ALL('Customer DB'[Cr. Name]))

RETURN

IF(ISBLANK([Prov USD]),BLANK(),

CALCULATE(

[Prov USD],

FILTER(ALL('Customer DB'),[Prov USD]>=thisCustProv)

))

Regards,

Ansar