cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper IV

## 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)
)
)

1 ACCEPTED SOLUTION
Super User

@bcobrien1977

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

Did I answer your question? Mark my post as a solution! and hit thumbs up
9 REPLIES 9
Super User

@bcobrien1977

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] )
)``````

Did I answer your question? Mark my post as a solution! and hit thumbs up
Helper IV

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?

Super User

@bcobrien1977

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')
)``````
Did I answer your question? Mark my post as a solution! and hit thumbs up
Helper IV

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.

Helper II

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.

Super User

@bcobrien1977

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

Did I answer your question? Mark my post as a solution! and hit thumbs up
Helper II

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

Helper IV

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
Helper II

Try This hope this help

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

Announcements

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

#### Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

#### Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors