Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Cumulative Sum on Text Field

Hi,

I am trying to present a cumulative sum by priority and project with the two being a concatenated field in a separate table.

 

Example. (Priority Initiative)

1-Warehouse

2-Field

3-Retail

11-Other

 

Sales Data

Priority 1 50

Priority 11 -200

 

 

 

I am trying to capture the sales and other data such that I can sort the matrix by the priority intiative and have it calculate correctly.

 
I am using this as my measure but it does not calculate correctly
 Running Total =
CALCULATE(
   sum('Sales'[Total Sales]),
    FILTER(
        ALLSELECTED('Priority Intiative'[Priority]),
        ISONORAFTER('Priority Intiative'[Priority], MAX('Priority Intiative'[Priority]), DESC)
    )
)

Thanks for your help. 

 

 

Need Help
  • Fowmy's avatar
    Fowmy
    2 years ago

    Anonymous 

    Please check the attached file:

    Running Total = 
CALCULATE(
    [Total Sales],
    'Sales Table'[Priortiy] <= MAX( 'Priority Table'[Priority] ),
    ALLSELECTED( 'Priority Table' )   
)

     

     


     

9 Replies

  • saudansari's avatar
    saudansari
    Helper II
    2 years ago

    So do your columns have numbers only or both numbers and text If yes, I recommend using a split column in Power Query and the sum with DAX. If this is not helping I will recommend creating a sample data and sharing the file with us to remove sensitive data 

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Hi, here is how things look....  Thanks!

       

      Sales Table     
      PriortiySalesTeam   
      150A   
      11200A   
      375B   
      250B   
            
      Priority Table     
      PriorityPriority Int    
      11-Warehouse    
      22-Field    
      33-Retail    
      1111-Other    
            
            
      Desired     
       Team A SalesTeam A CumulativeTeam BTeam B Cumulative
            
      1-Warehouse5050   
      2-Field 505050 
      3-Retail 5075125 
      11-Other200250   
      Total250250125125 
  • Fowmy's avatar
    Fowmy
    Super User
    2 years ago

    Anonymous 

    Modify your measure as follows. I assume you have includedonly the 'Priority Intiative'[Priority Int] on the Row section of the matrix, that's way I removed the filters using REMOVEFILTER, if you have added more columns from the same table, please include them as well.



    Running Total = 
CALCULATE(
   sum( Sales[Total Sales]),
      'Priority Intiative'[Priority] <=  MAX('Priority Intiative'[Priority]),
      REMOVEFILTERS( 'Priority Intiative'[Priority Int] )      
)

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Thanks!  I am still runnning into this thing where it doesn't carry the value down. My Priority table only has the Int field and the priority number.  Any thoughts? 

       

      • Fowmy's avatar
        Fowmy
        Super User
        2 years ago

        Anonymous 

        Try this measure, if it doesn't work, please share a dummy PBI file that represents your secenario. Save it in Google Drive and share the link here.

        Running Total = 
CALCULATE(
   sum( Sales[Total Sales]),
      'Priority Intiative'[Priority] <=  MAX('Priority Intiative'[Priority]),
      REMOVEFILTERS( 'Priority Intiative')      
)