cancel
Showing results for
Did you mean:

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Helper I

## Cumulative Running Total Issue with Legend

Hi all,

I have a cumulative column working using quick measure:

Cumulative Amount= CALCULATE (

SUM('Table'[Amount] ),
FILTER (ALLSELECTED('Table'),
'Table'[ORDER_DATE] <= MAX( 'Table'[ORDER_DATE] )
)
)

 ORDER_DATE Amount Cumulative Amount
 JAN 10 10 FEB 5 15 MAR 5 20

However when I add another column (client type) the monthly totals are duplicating inthe cumulative column.

How do I get cumulative totals per the client type by month?

 ORDER_DATE CLIENT TYPE Amount Cumulative Amount
 JAN A 2 10 JAN B 5 10 JAN C 3 10 FEB A 1 15 FEB B 3 15 FEB C 1 15 MAR A 3 20 MAR B 1 20 MAR C 1 20

So I would like this:

 ORDER_DATE CLIENT TYPE Amount Cumulative Amount
 JAN A 2 2 JAN B 5 5 JAN C 3 3 FEB A 1 3 FEB B 3 8 FEB C 1 4 MAR A 3 6 MAR B 1 9 MAR C 1 5
1 ACCEPTED SOLUTION
Super User

@jp2020 , Try if this can help

Cumulative Amount= CALCULATE (
SUM('Table'[Amount] ),
FILTER (ALLSELECTED('Table'),'NT Transactions'[Client_Type] =max('NT Transactions'[Client_Type] ) &&
'NT Transactions'[ORDER_DATE] <= MAX( 'NT Transactions'[ORDER_DATE] )
)
)

2 REPLIES 2
Super User

@jp2020 , Try if this can help

Cumulative Amount= CALCULATE (
SUM('Table'[Amount] ),
FILTER (ALLSELECTED('Table'),'NT Transactions'[Client_Type] =max('NT Transactions'[Client_Type] ) &&
'NT Transactions'[ORDER_DATE] <= MAX( 'NT Transactions'[ORDER_DATE] )
)
)

Helper I

That worked perfectly, thank you!

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

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

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors