Cumulative Sum on Text Field

[Anonymous]

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. 

[Fowmy]

@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]

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]

@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')      
)

[Anonymous]

https://drive.google.com/file/d/1k1M3yCb-4z1e_mF0hAT28SpeY3vdrX2P/view?usp=drive_link 

Hi, if you look at Team B, there is 592 for one priority and one for a second one. I would expect the cumulative to be 592 until the next one kicks in to make it 1192.  I need this to work with text though. 

[saudansari]

Hi, 

the shared file is different and does not reflect the number as you mentioned please share the file again. Create sample data and remove sensitive information. 

[Fowmy · Accepted solution]

@Anonymous 

Please check the attached file:

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

[saudansari]

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]

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

Sales Table
Priortiy	Sales	Team
1	50	A
11	200	A
3	75	B
2	50	B
Priority Table
Priority	Priority Int
1	1-Warehouse
2	2-Field
3	3-Retail
11	11-Other
Desired
	Team A Sales	Team A Cumulative	Team B	Team B Cumulative
1-Warehouse	50	50
2-Field		50	50	50
3-Retail		50	75	125
11-Other	200	250
Total	250	250	125	125

[saudansari]

Try This hope this help

https://drive.google.com/file/d/1k1M3yCb-4z1e_mF0hAT28SpeY3vdrX2P/view?usp=drivesdk


please make sure there is a relationship between the 'Priority' field in the 'Priority Table' and the 'Priority' field in the 'Sales Table'.