Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi,
I need to represent a report that shows weekly trend of YTD average of a sales per region. Any advise on how can I use Power BI to help me?
This is the example
Region Day Sales Amount
A 1 1000
A 2 1500
A 3 700
B 1 500
B 2 700
After Day 7, there should be a table that calculate Sales Amount Average from Day 1 to Day 7 per region
Region Sales Amount Average Day 1-7
A 1300
B 700
After Day 14, the table should calculate Sales Amount Average from Day 1 to Day 17 per region, and compare it with Sales Amount Average Day 1-7
Region Sales Amount Average Day 1-14 Sales Amount Average Day 1-7
A 1400 1300
B 650 700
Thanks
RMV
Solved! Go to Solution.
Hi @RMV,
You can create a measure in your resource table using the formula.
Sales Amount Average Day 1-7=CALCULATE(MAX(Table[running]),FILTER(Table,AND(Table[Day]<=7,Table[Day]>1)))
Sales Amount Average Day 1-14=CALCULATE(MAX(Table[running]),FILTER(Table,AND(Table[Day]<=14,Table[Day]>1)))
Then create a table visual, select [Region] and the measures as value levels.
Best Regards,
Angelia
Hi @RMV,
Althrough you give an detailed example, I still need to confirm several information.
>>After Day 7, there should be a table that calculate Sales Amount Average from Day 1 to Day 7 per region
For Region A, how to calculate 1300? based on my mind, it shoud be (1000+1500+700)/3=1067.
In addition, how to get 1400 and 650 after day 14? Could you please share more details for further analysis?
Best Regards,
Angelia
Hi @v-huizhn-msft,
The numbers were just an examples. The average should calculate the numbers from Day 1-7, Day 1-14, Day 1-21 etc
The first table is also just an example to show that the sales is keep in raw data, day per day transactions, and not using the complete data of all days to keep my post short. Hope this is clear.
Waiting for input from you.
Thanks
Hi @RMV,
You can create a measure in your resource table using the formula.
Sales Amount Average Day 1-7=CALCULATE(MAX(Table[running]),FILTER(Table,AND(Table[Day]<=7,Table[Day]>1)))
Sales Amount Average Day 1-14=CALCULATE(MAX(Table[running]),FILTER(Table,AND(Table[Day]<=14,Table[Day]>1)))
Then create a table visual, select [Region] and the measures as value levels.
Best Regards,
Angelia
Hi @v-huizhn-msft,
Tried again with you DAX formula suggestion, and i got it worked!
Thanks a lot!
Regards,
Hi @v-huizhn-msft,
I'm not used with Measure column, and need more some explanation. I added 2 Measures to the table, and use them as Value in the visual, as you said. But it didn't shown anything. Need your advise.
Thanks,
RMV
User | Count |
---|---|
93 | |
83 | |
77 | |
75 | |
66 |
User | Count |
---|---|
115 | |
105 | |
93 | |
65 | |
60 |