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 need to calculate an "Average Order Intake" for "this year" - rolling to work next year as well.
I have this data:
Segmentation (A,B,C)
OrderStartDate (dates)
OnboardingDate (dates)
Order Intake Value (EUR)
It needs these 3 filters:
1. OrderStartDate (should be this year)
2. OnboardingDate (Should be this year)
3. Segment (SegmentA)
I simply can't make the measure work. Anyone who can help?
Solved! Go to Solution.
@PeterStuhr sorry this sounds confusing, based on number of accounts, or number of companies?
also i get a blank result from your data because there are no company accounts onboarded with segment a in 2019
this is the measure i used
Proud to be a Super User!
@PeterStuhr are you able to provide some data?
Proud to be a Super User!
Hi @vanessafvg
It could look like this:
Ordernr | OnboardingDate | OrderDate | Segment | OrderIntake |
1 | 01-01-2018 | 01-03-2016 | A | 1000 |
2 | 11-01-2019 | 03-08-2016 | A | 1240 |
3 | 06-08-2017 | 09-08-2016 | A | 3453 |
4 | 14-01-2019 | 09-08-2016 | C | 1119 |
5 | 05-01-2018 | 08-01-2017 | B | 4523 |
6 | 13-04-2019 | 08-01-2017 | A | 9999 |
7 | 15-01-2019 | 10-01-2017 | B | 29999 |
8 | 03-08-2016 | 06-08-2017 | A | 2343 |
9 | 12-01-2019 | 04-01-2018 | A | 1256 |
10 | 04-01-2018 | 07-01-2018 | A | 2134 |
11 | 10-01-2017 | 02-07-2018 | C | 2305 |
12 | 07-01-2018 | 11-01-2019 | B | 10000 |
13 | 08-01-2017 | 12-01-2019 | B | 2330 |
14 | 16-10-2019 | 15-01-2019 | C | 39999 |
15 | 02-07-2018 | 01-02-2019 | A | 2000 |
16 | 09-08-2016 | 13-04-2019 | C | 5430 |
17 | 17-01-2019 | 16-10-2019 | A | 55999 |
So I would like to calculate an average Order Intake for Segment A, for this year (both order date and onboarding date) 🙂
Thanks!
ok @PeterStuhr
i might be simplifying this
but basically i just created a card, set the aggregation to average and then dragged my filterds into the visual level filters and used relative date filtering for the dates - what are you expecting?
and then dra
Proud to be a Super User!
Hi again
Actually I explained this wrong.
It is 2 tables I have "related" to each other.
One of the tables contains the "Company" data. And the other one the Order Intake Data.
So instead of "average" of the order intake, the average should be based on the number of accounts.
Company table:
Company | Onboarding Date | Segment |
Test1 | 01-01-2016 | A |
Test2 | 02-01-2016 | A |
Test3 | 03-01-2016 | A |
Test4 | 04-01-2017 | B |
Test5 | 05-01-2017 | B |
Test6 | 06-01-2017 | B |
Test7 | 07-01-2018 | C |
Test8 | 08-01-2016 | B |
Test9 | 09-01-2019 | A |
Test10 | 10-01-2019 | C |
Test11 | 11-01-2019 | B |
Test12 | 12-01-2019 | A |
Order Intake table:
OrderDate | Company | EUR |
11-01-2019 | Test1 | 1000 |
12-01-2019 | Test2 | 2000 |
03-01-2016 | Test1 | 5000 |
04-01-2017 | Test1 | 50 |
11-01-2019 | Test2 | 100 |
12-01-2019 | Test10 | 300 |
07-01-2018 | Test3 | 2000 |
08-01-2016 | Test4 | 2000 |
09-01-2019 | Test2 | 3000 |
10-01-2019 | Test2 | 1000 |
11-01-2019 | Test3 | 2000 |
12-01-2019 | Test1 | 4000 |
So what I want: Average Order Intake Per Company. Filter: 2019 in both date fields, and Segment A. 🙂
@PeterStuhr sorry this sounds confusing, based on number of accounts, or number of companies?
also i get a blank result from your data because there are no company accounts onboarded with segment a in 2019
this is the measure i used
Proud to be a Super User!
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 |
---|---|
107 | |
93 | |
77 | |
65 | |
53 |
User | Count |
---|---|
147 | |
106 | |
104 | |
87 | |
61 |