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 all,
Need advise on syntax to use to calculate below case.
I have a calculation of total figure of an average sales amount for all regions YTD.
I used this calculation to calculate the total figure: SUMX(VALUES(Region[RegionName]),[Average of Sales Amount YTD])
The average calculation is: CALCULATE([Total Amount],FILTER(Sales,AND(MIN(Sales[sales_date]),MAX(Sales[sales_date])))) / CALCULATE(DISTINCTCOUNT(Sales[sales_date]),FILTER(Sales,AND(MIN(Sales[sales_date]),MAX(Sales[sales_date]))))
Now, I need to create a comparison of the total figure of an average sales amount for all regions per end of month & per end of week. This is the condition I'm looking for (assuming that this graph/table is seen today 29-May-17):
Per month
Jan-17 (Total figure of average sales amount for all regions as of Jan-17)
Feb-17 (Total figure of average sales amount for all regions as of Feb-17)
Mar-17 (Total figure of average sales amount for all regions as of Mar-17)
Apr-17 (Total figure of average sales amount for all regions as of Apr-17)
29-May-17 Total figure of average sales amount for all regions as of Today 29-May-17)
Per week
same concept as above, but calculated per week
Please kindly help on how to calculate per month & per week figure.
Appreciate it
Regards,
Solved! Go to Solution.
Hi @RMV,
My mistake!
The formula below should work.
Average of Sales Amount YTD = VAR currentDate = MAX ( Sales[Date] ) VAR currentRegion = FIRSTNONBLANK ( Sales[Region], 1 ) RETURN CALCULATE ( AVERAGE ( Sales[Sales Amount] ), FILTER ( ALL ( Sales ), Sales[Date] <= currentDate && Sales[Region] = currentRegion ) )
Total Average = SUMX ( VALUES ( Sales[Region] ), [Average of Sales Amount YTD] )
Regards
Hi @v-ljerr-msft,
Sure. Sorry for not being detail on the case.
I currently have Sales Table
Date Region Sales Amount
1-Jan-17 A 1000
1-Jan-17 B 1200
1-Jan-17 C 500
2-Jan-17 A 1050
2-Jan-17 C 700
3-Jan-17 B 1500
etc
Currently I already have calculation of the total of average from each region YTD.
For example, this is the figure of 29-May-17 (consistent with the previous example that for example current date is 29-May), if we visualize it using Table
Region Average from 1-Jan-17 to 30-May-17 per Region
A 1200
B 1500
C 550
Total 3250 (1200 + 1500 + 550)
Then, what I'm looking for is the trend of the total of average above per month or until current date
X axis is month, the values in each month is
1. Total of average from 1-Jan to 31-Jan for Jan axis
2. Total of average from 1-Jan to 28-Feb for Feb axis
3. Total of average from 1-Jan to 31-Mar for Mar axis
4. Total of average from 1-Jan to 30-Apr for Apr axis
5. Total of average from 1-Jan to 29-May (current date) for May axis
6. moving forward
Looking forward for your help.
Thank you
Hi @RMV,
Could you try the formula below to see if it works in your scenario?
Average of Sales Amount YTD = VAR currentDate = MAX ( Sales[sales_date] ) RETURN CALCULATE ( AVERAGE ( Sales[Sales Amount] ), FILTER ( ALL ( Sales ), Sales[sales_date] <= currentDate ) )
Total Average = SUMX ( VALUES ( Region[RegionName] ), [Average of Sales Amount YTD] )
Regards
Hi @v-ljerr-msft,
It's not working
This is the data sample
Date | Month | Region | Sales Amount |
1-Jan-17 | 1 | A | 1000 |
1-Jan-17 | 1 | B | 1200 |
1-Jan-17 | 1 | C | 500 |
2-Jan-17 | 1 | A | 1050 |
2-Jan-17 | 1 | C | 700 |
3-Jan-17 | 1 | B | 1500 |
4-Jan-17 | 1 | A | 1100 |
4-Jan-17 | 1 | B | 1500 |
2-Feb-17 | 2 | A | 1300 |
2-Feb-17 | 2 | C | 400 |
5-Feb-17 | 2 | A | 1200 |
5-Feb-17 | 2 | B | 1700 |
5-Feb-17 | 2 | C | 600 |
The result i got by applying formula given from you is
January = 3206.25
February = 3173.08
While it should be:
January = 3050
February = 3155
This came from below calculation
Region Jan Sales Amount Jan Average Jan-Feb Sales Amount Jan-Feb Average
total of sales amount per region in Jan average of sales amount in Jan total of sales amount per region in Jan-Feb total of sales amount Jan-Feb
A 3150 (1000 + 1050 + 1100) 1050 (3150 / 3) 5650 (1000 + 1050 + 1100 + 1300 + 1200) 1130 (5650 / 5)
B 4200 (1200 + 1500 + 1500) 1400 (4200 / 3) 5900 (1200 + 1500 + 1500) + 1700) 1475 (5900 / 4)
C 1200 (500 + 700) 600 (1200 / 2) 2200 (500 + 700 + 400 + 600) 550 (2200 / 4)
Total of average per region 3050 (1050 + 1400 + 600) 3155 (1130 + 1475 + 550)
any advise?
Hi @RMV,
My mistake!
The formula below should work.
Average of Sales Amount YTD = VAR currentDate = MAX ( Sales[Date] ) VAR currentRegion = FIRSTNONBLANK ( Sales[Region], 1 ) RETURN CALCULATE ( AVERAGE ( Sales[Sales Amount] ), FILTER ( ALL ( Sales ), Sales[Date] <= currentDate && Sales[Region] = currentRegion ) )
Total Average = SUMX ( VALUES ( Sales[Region] ), [Average of Sales Amount YTD] )
Regards
Hi @RMV,
Could you post your table structures with some sample data and your expected result? So that we can better assist on this issue.
Regards
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 |
---|---|
104 | |
101 | |
79 | |
72 | |
64 |
User | Count |
---|---|
142 | |
108 | |
101 | |
81 | |
74 |