cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
MunroA7
Advocate III
Advocate III

Cumulative total by band within a date and revenue range

Hi,

I have a report with sales data and a set of revenue bands.  Each customer has a set of bands with a start and end date plus a min and max revenue range per band.  Each deal is unique to that customer (i.e. they have various numbers of bands with different start and end ranges per client).  

I'm trying to get a running total within each band, where it 'carries over' revenue spent to the next band.  I've used the DAX cumulative example from SQLBI and I've got what I need for running total per deal but it's not quite right for measuring their spend per revenue target. 

 

 

 

 

 

Sales Seg. Customers = 
VAR CustomersInSegment =                        -- Gets the customers in current segment
    FILTER (
        ALLSELECTED ( 'Dim.Deal'[Customer] ),
        VAR SalesOfCustomer = [Sales Amount] -- Computes Sales Amount for one customer
        VAR SegmentForCustomer =                -- Retrieves the segment
            FILTER (                            -- a customer belongs to
                'Dim.Deal',
                NOT ISBLANK ( SalesOfCustomer )
                    && 'Dim.Deal'[From] < SalesOfCustomer
                    && 'Dim.Deal'[To] >= SalesOfCustomer
            )
        VAR IsCustomerInSegments = NOT ISEMPTY ( SegmentForCustomer )
        RETURN IsCustomerInSegments
    )
VAR Result =
    CALCULATE (
        [Sales Amount],                        -- Expression to compute
        KEEPFILTERS ( CustomersInSegment )     -- Applies filter for segmented customers
    )
RETURN
    Result      

 

 

 

 

In my PBIX file I get this result:-

band resutls.png

 

But what I also want is like this, so it takes the 300k above and runs is across the bands so we can see which band number/range they have met so far and I can use that for something like percentage achieved per band and so on.

 

From To Range Target Achieved
0 150000 150000  150000
150001 275000 124999  124999
275001 400000 124999    24999
400001 10000000 9599999            0

I can't quite work out how to do the calculation to 'carry over' with the running total to the next row once they've reached the target on the row before and run that per customer deal.

Can anyone point me to the DAX that might help to achieve this please.  PBIX here .  Thank you.

1 ACCEPTED SOLUTION
Jihwan_Kim
Community Champion
Community Champion

Hi, @MunroA7 

Please correct me if I wrongly understood your data model.

Please check  the below picture and the sample pbix file's link down below.

 

Picture1.png

 

Sales total within range =
IF (
IF (
[Sales] >= MAX ( 'Dim.Deal'[To] ),
MAX ( 'Dim.Deal'[To] ),
[Sales] - MAX ( 'Dim.Deal'[From] )
) < 0,
0,
IF (
[Sales] >= MAX ( 'Dim.Deal'[To] ),
MAX ( 'Dim.Deal'[To] ),
[Sales] - MAX ( 'Dim.Deal'[From] )
)
)

 

https://www.dropbox.com/s/rz18wuq24h73b45/Values%20per%20band.pbix?dl=0 

 

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

View solution in original post

3 REPLIES 3
Jihwan_Kim
Community Champion
Community Champion

Hi, @MunroA7 

Please correct me if I wrongly understood your data model.

Please check  the below picture and the sample pbix file's link down below.

 

Picture1.png

 

Sales total within range =
IF (
IF (
[Sales] >= MAX ( 'Dim.Deal'[To] ),
MAX ( 'Dim.Deal'[To] ),
[Sales] - MAX ( 'Dim.Deal'[From] )
) < 0,
0,
IF (
[Sales] >= MAX ( 'Dim.Deal'[To] ),
MAX ( 'Dim.Deal'[To] ),
[Sales] - MAX ( 'Dim.Deal'[From] )
)
)

 

https://www.dropbox.com/s/rz18wuq24h73b45/Values%20per%20band.pbix?dl=0 

 

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

View solution in original post

Jihwan_Kim
Community Champion
Community Champion

Hi, @MunroA7 

Please check your link.

I think the link requests the password.

Sorry, should be viewable now.

 

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

Top Solution Authors
Top Kudoed Authors