Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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:-
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.
Solved! Go to Solution.
Hi, @Anonymous
Please correct me if I wrongly understood your data model.
Please check the below picture and the sample pbix file's link down below.
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.
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Hi, @Anonymous
Please correct me if I wrongly understood your data model.
Please check the below picture and the sample pbix file's link down below.
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.
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Hi, @Anonymous
Please check your link.
I think the link requests the password.
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Sorry, should be viewable now.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
97 | |
85 | |
68 | |
59 |
User | Count |
---|---|
150 | |
120 | |
100 | |
87 | |
68 |