Forum Discussion

ansar's avatar
ansar
Helper II
4 years ago
Solved

Incorrect Cumulative Total / Running Total - Text field

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.

Need Help
  • ansar's avatar
    ansar
    4 years ago

    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



4 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    4 years ago

    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's avatar
      ansar
      Helper II
      4 years ago

      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. NameTypePstng DateLCurrAgreement #Agmt TypeAmt
      Union CoopBF31/01/20AED11339BDA     (706,603.70)
      Union CoopBF29/02/20AED11339BDA       706,603.70
      Union CoopBF29/02/20AED11339BDA     (536,966.65)
      Union CoopBF31/03/20AED11339BDA       536,966.65
      Union CoopBF31/03/20AED11339BDA  (1,157,831.71)
      Union CoopRV12/03/20AED11339BDA       216,362.47
      Union CoopRV26/03/20AED11339BDA       320,604.18
      Union CoopBF30/04/20AED11339BDA    1,157,831.71
      Union CoopBF30/04/20AED11339BDA  (2,065,185.54)
      Union CoopRV12/04/20AED11339BDA       620,865.06
      Union CoopBF31/05/20AED11339BDA    2,065,185.54
      Union CoopBF31/05/20AED11339BDA  (2,435,021.28)
      Union CoopRV16/05/20AED11339BDA       453,676.92
      Union CoopBF30/06/20AED11339BDA    2,435,021.28
      Union CoopBF30/06/20AED11339BDA  (2,530,466.04)
      Union CoopRV05/06/20AED11339BDA       145,287.86
      Union CoopRV14/06/20AED11339BDA       339,112.40
      Union CoopRV17/06/20AED11339BDA       339,112.39
      Union CoopBF31/07/20AED11339BDA    2,530,466.04
      Union CoopBF31/07/20AED11339BDA  (2,631,583.72)
      Union CoopRV16/07/20AED11339BDA         95,444.76
      Union CoopBF31/08/20AED11339BDA    2,631,583.72
      Union CoopBF31/08/20AED11339BDA  (2,943,629.67)
      Union CoopRV09/08/20AED11339BDA       101,117.68
      Union CoopBF30/09/20AED11339BDA    2,943,629.67
      Union CoopBF30/09/20AED11339BDA  (3,116,977.41)
      Union CoopRV09/09/20AED11339BDA       312,045.95
      Union CoopBF31/10/20AED11339BDA    3,116,977.41
      Union CoopBF31/10/20AED11339BDA  (3,115,166.70)
      Union CoopRV31/10/20AED11339BDA       170,000.00
      Union CoopBF30/11/20AED11339BDA    3,115,166.70
      Union CoopBF30/11/20AED11339BDA  (3,358,620.84)
      Union CoopBF31/12/20AED11339BDA    3,358,620.84
      Union CoopBF31/12/20AED11339BDA  (3,588,053.31)
      Union CoopRV14/12/20AED11339BDA       240,000.00
      Union CoopRV27/01/21AED11339BDA            4,000.00
      Union CoopRV07/02/21AED11339BDA       230,423.64
      CarrefourBF30/11/20SAR18927Punctual        (15,000.00)
      CarrefourRV31/12/20SAR18927Punctual         15,000.00
      CarrefourRV31/07/21SAR18927Punctual        (15,000.00)
      CarrefourRV31/07/21SAR18927Punctual         15,000.00
      TescoBF30/04/21SAR24426Sell Out        (20,448.60)
      TescoBF26/05/21SAR24426Sell Out         20,448.60
      TescoBF26/05/21SAR24426Sell Out        (17,623.48)
      TescoRV30/06/21SAR24426Sell Out         13,098.00
      TescoRV31/07/21SAR24426Sell Out            4,525.48

       

      Regards,

      Ansar.