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'm trying to calculate the top 10 sales in a 3month period grouped by the salesperson,state and customer. Here's a link to the pbix TopNSalesRecent @ The expected results are in the Top Recent Sales Column (hard coded values to show example of what is expected, circled in Blue). My measure is Measure for Top Recent Sales (circled in Red). My measure is
Measure for Top Recent Sales =
var TopNo = 10
var DaysBefore = -90
var NoOfSales = [Sales]
var LastDatePeriod = LASTDATE(DimDates[Date])
VAR Period = DATESINPERIOD(DimDates[Date],LastDatePeriod,DaysBefore,DAY)
VAR FirstDatePeriod = MINX(Period,DimDates[Date])
VAR Result =
CALCULATETABLE(
TOPN(
TopNo,
SUMMARIZE(
data,
DimDates[Date],
data[Customer ID],
data[Salesperson ID],
data[State],
"NoOfSales",NoOfSales
),
DimDates[Date]
,DESC
),
FILTER(ALL(DimDates[Date]),DimDates[Date]>= FirstDatePeriod && DimDates[Date] <= LastDatePeriod),
FILTER(data,NoOfSales>0)
)
RETURN
IF(FirstDatePeriod <= LastDatePeriod,
SUMX(Result,[NoOfSales]))
@Jihwan_Kim here is the question I had
Solved! Go to Solution.
@Anonymous
Hi,
Please try the below.
I am not sure whether it gives the correct result or not, but I think you can try to create similar to the below measures.
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.
Linkedin: linkedin.com/in/jihwankim1975/
Twitter: twitter.com/Jihwan_JHKIM
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
May I ask how do you calculate the blue color circle?
For instance, I want to know how to calculate to have the result of 50 for April 1st 2020.
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.
I calculate that by adding the latest 10 sales which is 50 its the total from 12 March to April 1st 2020 grouped by that salesperson,customer and state. I hardcoded in the Excel source data file to give example of how the measure should look like
Hi, @Anonymous
I still cannot understand why the 31st March's result is also 50.
I tried to create a measure based on my assumption.
Please check the below.
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.
All sales which fall between 12 March and 1 April form part of the latest 10 sales which if all sales are summed up between that range is 50
@Anonymous
Hi,
Please try the below.
I am not sure whether it gives the correct result or not, but I think you can try to create similar to the below measures.
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.
Linkedin: linkedin.com/in/jihwankim1975/
Twitter: twitter.com/Jihwan_JHKIM
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.
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 |
---|---|
47 | |
22 | |
20 | |
15 | |
13 |
User | Count |
---|---|
49 | |
41 | |
39 | |
19 | |
19 |