Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

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.

Reply
soxy
Frequent Visitor

Calculate average by month and filter by monthly value

‎05-12-2022 01:33 AM

Hey guys hope you are doing well, 

 

I've got a tricky calculation where I run out of ideas. Please find the screenshot below as example. 

I've got a period in months (dimDate[MonthName]). Every month has a amount of bookings (FactBooking[HoursBooked])

For the whole period I want the average amount which is over the budget and the amount which is below the budget.

 

soxy_1-1652343339719.png

You can see 7 months and every month has a budget as a green line (dimBudget[Budget]). 

 

For the overbudget I want to achieve the following:

14,5 / 5 (Budget) / 7 (period length/months) = ~0,4143

Here it should only be used the february for calculation because its over budget

 

The second measure should be the under budget and give me the following value: 

(1- (1.5 / 5)) + (1 - (0 / 5) + (1 - (1/5)) + (1- (0/5)) = 3.5 / 7 (period length/months) = 0,5

here its should only be the october, november, december and january in the calculation because they are under budget. 

 

Columns where Budget and BookedHours are the same (april and march) shouldn't go in the calculation for over or under budget.

 

For the period i use the following which is working fine:

var Period= DATEDIFF(Calculate(FIRSTDATE(FactBooking[DateBooked]), ALLSELECTED(dimDate[Dates])), Calculate(LASTDATE(FactBooking[DateBooked]), ALLSELECTED(dimDate[Dates])), MONTH) + 1
 
This is the measure for over budget:
Over Budget= 
//For the Budget I use the following:
var BudgetTotal= (Sum(dimBudget[Budget]))
 
//For the BookedHours I use the following:
var AmountBooked= AVERAGEX(
SUMMARIZE(FactBooking, dimDate[Month]),
CALCULATE( SUM(FactBooking[HoursBooked]))
)
 
//And then I tried this return with an If statement and another calcualation before for a monthly base but I cant get it to work. 
Return 1- DIVIDE(DIVIDE(AmountBooked,BudgetTotal) , Period)
 
I hope you can help me out with this one and many many thanks in advance! 
See you!
Labels:
Message 1 of 8
921 Views
0
7 REPLIES 7
v-yalanwu-msft
Community Support
Community Support

‎05-16-2022 02:49 AM

Hi, @soxy ;

Please refer to this post which have simple file.

https://community.powerbi.com/t5/Desktop/How-to-compute-due-date-base-on-invoice-date-and-Credit-ter...


Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

How to get your questions answered quickly -- How to provide sample data
Message 8 of 8
782 Views
0
v-yalanwu-msft
Community Support
Community Support

‎05-15-2022 08:27 PM

Hi, @soxy ;

Please try it.

overbudget = CALCULATE( SUM([value]),FILTER('Table',[value]>5))/5/ DISTINCTCOUNT([Month])
under budget = 
var _count=CALCULATE(DISTINCTCOUNT([Month]),FILTER(ALL('Table'),[value]<5))
var _sum=CALCULATE(SUM([value]),FILTER(ALL('Table'),[value]<5))
return  (_count-(_sum/5))/DISTINCTCOUNT('Table'[Month])

The final show as follow:

vyalanwumsft_0-1652671642495.png


Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

How to get your questions answered quickly -- How to provide sample data
Calculate average by month and filter by monthly.pbix
Message 6 of 8
820 Views
0
soxy
Frequent Visitor
In response to v-yalanwu-msft

‎05-16-2022 01:39 AM

Hi! Thank you for your reply. I realized that I should mention that the Budget is not always 5. It worked only for this exact one example. 

You can see the table dimCostCenter[Budget] for a detailed look. I couldn't get it to work with my data. 

Message 7 of 8
798 Views
0
lbendlin
Super User
Super User

‎05-14-2022 12:44 PM

Please provide sanitized sample data that fully covers your issue. If you paste the data into a table in your post or use one of the file services it will be easier to assist you. Avoid posting screenshots of your source data if possible.

Please show the expected outcome based on the sample data you provided. Screenshots of the expected outcome are ok.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

Message 2 of 8
848 Views
0
soxy
Frequent Visitor
In response to lbendlin

‎05-16-2022 01:37 AM
01. Feb 222022211Februar2022|2
02. Feb 222022221Februar2022|2
03. Feb 222022231Februar2022|2
04. Feb 222022241Februar2022|2
05. Feb 222022251Februar2022|2
06. Feb 222022261Februar2022|2
07. Feb 222022271Februar2022|2
08. Feb 222022281Februar2022|2
09. Feb 222022291Februar2022|2
10. Feb 2220222101Februar2022|2
11. Feb 2220222111Februar2022|2
12. Feb 2220222121Februar2022|2
13. Feb 2220222131Februar2022|2
14. Feb 2220222141Februar2022|2
15. Feb 2220222151Februar2022|2
16. Feb 2220222161Februar2022|2
17. Feb 2220222171Februar2022|2
18. Feb 2220222181Februar2022|2
19. Feb 2220222191Februar2022|2
20. Feb 2220222201Februar2022|2
21. Feb 2220222211Februar2022|2
22. Feb 2220222221Februar2022|2
23. Feb 2220222231Februar2022|2
24. Feb 2220222241Februar2022|2
25. Feb 2220222251Februar2022|2
26. Feb 2220222261Februar2022|2
27. Feb 2220222271Februar2022|2
28. Feb 2220222281Februar2022|2
01. Mrz 222022311März2022|3
02. Mrz 222022321März2022|3
03. Mrz 222022331März2022|3
04. Mrz 222022341März2022|3
05. Mrz 222022351März2022|3
06. Mrz 222022361März2022|3
07. Mrz 222022371März2022|3
08. Mrz 222022381März2022|3
09. Mrz 222022391März2022|3
10. Mrz 2220223101März2022|3
11. Mrz 2220223111März2022|3
12. Mrz 2220223121März2022|3
13. Mrz 2220223131März2022|3
14. Mrz 2220223141März2022|3
15. Mrz 2220223151März2022|3
16. Mrz 2220223161März2022|3
17. Mrz 2220223171März2022|3
18. Mrz 2220223181März2022|3
19. Mrz 2220223191März2022|3
20. Mrz 2220223201März2022|3
21. Mrz 2220223211März2022|3
22. Mrz 2220223221März2022|3
23. Mrz 2220223231März2022|3
24. Mrz 2220223241März2022|3
25. Mrz 2220223251März2022|3
26. Mrz 2220223261März2022|3
27. Mrz 2220223271März2022|3
28. Mrz 2220223281März2022|3
29. Mrz 2220223291März2022|3
30. Mrz 2220223301März2022|3
31. Mrz 2220223311März2022|3
01. Apr 222022412April2022|4
02. Apr 222022422April2022|4
03. Apr 222022432April2022|4
04. Apr 222022442April2022|4
05. Apr 222022452April2022|4
06. Apr 222022462April2022|4
07. Apr 222022472April2022|4
08. Apr 222022482April2022|4
09. Apr 222022492April2022|4
10. Apr 2220224102April2022|4
11. Apr 2220224112April2022|4
12. Apr 2220224122April2022|4
13. Apr 2220224132April2022|4
14. Apr 2220224142April2022|4
15. Apr 2220224152April2022|4
16. Apr 2220224162April2022|4
17. Apr 2220224172April2022|4
18. Apr 2220224182April2022|4
19. Apr 2220224192April2022|4
20. Apr 2220224202April2022|4
21. Apr 2220224212April2022|4
22. Apr 2220224222April2022|4
23. Apr 2220224232April2022|4
24. Apr 2220224242April2022|4
25. Apr 2220224252April2022|4
26. Apr 2220224262April2022|4
27. Apr 2220224272April2022|4
28. Apr 2220224282April2022|4
29. Apr 2220224292April2022|4
30. Apr 2220224302April2022|4
Message 5 of 8
799 Views
0
soxy
Frequent Visitor
In response to lbendlin

‎05-16-2022 01:36 AM

dimDate:

01. Okt 2120211014Oktober2021|10
02. Okt 2120211024Oktober2021|10
03. Okt 2120211034Oktober2021|10
04. Okt 2120211044Oktober2021|10
05. Okt 2120211054Oktober2021|10
06. Okt 2120211064Oktober2021|10
07. Okt 2120211074Oktober2021|10
08. Okt 2120211084Oktober2021|10
09. Okt 2120211094Oktober2021|10
10. Okt 21202110104Oktober2021|10
11. Okt 21202110114Oktober2021|10
12. Okt 21202110124Oktober2021|10
13. Okt 21202110134Oktober2021|10
14. Okt 21202110144Oktober2021|10
15. Okt 21202110154Oktober2021|10
16. Okt 21202110164Oktober2021|10
17. Okt 21202110174Oktober2021|10
18. Okt 21202110184Oktober2021|10
19. Okt 21202110194Oktober2021|10
20. Okt 21202110204Oktober2021|10
21. Okt 21202110214Oktober2021|10
22. Okt 21202110224Oktober2021|10
23. Okt 21202110234Oktober2021|10
24. Okt 21202110244Oktober2021|10
25. Okt 21202110254Oktober2021|10
26. Okt 21202110264Oktober2021|10
27. Okt 21202110274Oktober2021|10
28. Okt 21202110284Oktober2021|10
29. Okt 21202110294Oktober2021|10
30. Okt 21202110304Oktober2021|10
31. Okt 21202110314Oktober2021|10
01. Nov 2120211114November2021|11
02. Nov 2120211124November2021|11
03. Nov 2120211134November2021|11
04. Nov 2120211144November2021|11
05. Nov 2120211154November2021|11
06. Nov 2120211164November2021|11
07. Nov 2120211174November2021|11
08. Nov 2120211184November2021|11
09. Nov 2120211194November2021|11
10. Nov 21202111104November2021|11
11. Nov 21202111114November2021|11
12. Nov 21202111124November2021|11
13. Nov 21202111134November2021|11
14. Nov 21202111144November2021|11
15. Nov 21202111154November2021|11
16. Nov 21202111164November2021|11
17. Nov 21202111174November2021|11
18. Nov 21202111184November2021|11
19. Nov 21202111194November2021|11
20. Nov 21202111204November2021|11
21. Nov 21202111214November2021|11
22. Nov 21202111224November2021|11
23. Nov 21202111234November2021|11
24. Nov 21202111244November2021|11
25. Nov 21202111254November2021|11
26. Nov 21202111264November2021|11
27. Nov 21202111274November2021|11
28. Nov 21202111284November2021|11
29. Nov 21202111294November2021|11
30. Nov 21202111304November2021|11
01. Dez 2120211214Dezember2021|12
02. Dez 2120211224Dezember2021|12
03. Dez 2120211234Dezember2021|12
04. Dez 2120211244Dezember2021|12
05. Dez 2120211254Dezember2021|12
06. Dez 2120211264Dezember2021|12
07. Dez 2120211274Dezember2021|12
08. Dez 2120211284Dezember2021|12
09. Dez 2120211294Dezember2021|12
10. Dez 21202112104Dezember2021|12
11. Dez 21202112114Dezember2021|12
12. Dez 21202112124Dezember2021|12
13. Dez 21202112134Dezember2021|12
14. Dez 21202112144Dezember2021|12
15. Dez 21202112154Dezember2021|12
16. Dez 21202112164Dezember2021|12
17. Dez 21202112174Dezember2021|12
18. Dez 21202112184Dezember2021|12
19. Dez 21202112194Dezember2021|12
20. Dez 21202112204Dezember2021|12
21. Dez 21202112214Dezember2021|12
22. Dez 21202112224Dezember2021|12
23. Dez 21202112234Dezember2021|12
24. Dez 21202112244Dezember2021|12
25. Dez 21202112254Dezember2021|12
26. Dez 21202112264Dezember2021|12
27. Dez 21202112274Dezember2021|12
28. Dez 21202112284Dezember2021|12
29. Dez 21202112294Dezember2021|12
30. Dez 21202112304Dezember2021|12
31. Dez 21202112314Dezember2021|12
01. Jan 222022111Januar2022|1
02. Jan 222022121Januar2022|1
03. Jan 222022131Januar2022|1
04. Jan 222022141Januar2022|1
05. Jan 222022151Januar2022|1
06. Jan 222022161Januar2022|1
07. Jan 222022171Januar2022|1
08. Jan 222022181Januar2022|1
09. Jan 222022191Januar2022|1
10. Jan 2220221101Januar2022|1
11. Jan 2220221111Januar2022|1
12. Jan 2220221121Januar2022|1
13. Jan 2220221131Januar2022|1
14. Jan 2220221141Januar2022|1
15. Jan 2220221151Januar2022|1
16. Jan 2220221161Januar2022|1
17. Jan 2220221171Januar2022|1
18. Jan 2220221181Januar2022|1
19. Jan 2220221191Januar2022|1
20. Jan 2220221201Januar2022|1
21. Jan 2220221211Januar2022|1
22. Jan 2220221221Januar2022|1
23. Jan 2220221231Januar2022|1
24. Jan 2220221241Januar2022|1
25. Jan 2220221251Januar2022|1
26. Jan 2220221261Januar2022|1
27. Jan 2220221271Januar2022|1
28. Jan 2220221281Januar2022|1
29. Jan 2220221291Januar2022|1
30. Jan 2220221301Januar2022|1
31. Jan 2220221311Januar2022|1
Message 4 of 8
801 Views
0
soxy
Frequent Visitor
In response to lbendlin

‎05-16-2022 01:34 AM

Hi!

Thank you for the reply. See sample data below. Sorry I wasn't able to attach a .xlsx file so I copyed the table in here. For the visualisation I need this 3 tables. 

 

Please keep in mind that the Budget by CostCenter is not always 5. See dimCostsCenter[Budget]

Expected Outcome a KPI in a card visual with the following data: 

Underbudget = 50 %
( (1- (1.5 / 5)) + (1 - (0 / 5) + (1 - (1/5)) + (1- (0/5)) = 3.5 / 7 (period length/months) = 0,5)

 

Overbudget = 42%

14,5 / 5 (Budget) / 7 (period length/months) = ~0,4143

 

 

FactBooking:

BookingDateCostCenterIDHoursBooked
Montag, 1. November 202110
Mittwoch, 1. Dezember 202110
Donnerstag, 6. Januar 202210
Mittwoch, 2. Februar 202210
Mittwoch, 2. März 202210
Dienstag, 5. April 202210
Freitag, 18. Februar 202213
Dienstag, 22. Februar 202214
Dienstag, 22. Februar 202213
Mittwoch, 26. Januar 202210,5
Donnerstag, 24. Februar 202210,5
Freitag, 1. April 202212
Freitag, 1. April 202212
Montag, 29. November 202111
Freitag, 21. Januar 202211
Freitag, 18. Februar 202211
Freitag, 18. Februar 202211
Dienstag, 22. Februar 202211
Dienstag, 22. Februar 202211
Freitag, 4. März 202211
Freitag, 4. März 202211
Mittwoch, 9. März 202211
Donnerstag, 17. März 202211
Donnerstag, 24. März 202211
Freitag, 1. April 202211
Dienstag, 26. Oktober 202110
Dienstag, 26. Oktober 202110
Dienstag, 16. November 202110
Mittwoch, 17. November 202110
Donnerstag, 16. Dezember 202110
Freitag, 17. Dezember 202110
Mittwoch, 19. Januar 202210
Donnerstag, 20. Januar 202210
Mittwoch, 16. Februar 202210
Freitag, 18. Februar 202210
Mittwoch, 23. März 202210
Dienstag, 29. März 202210

dimCostCenter:

CostCenterIDCostCenterNameBudget
1Name15
2Name210
3Name332
4Name4432
5Name55
6Name6123
7Name7353
8Name898
9Name975
10Name10345
11Name1170
12Name1298
13Name1316
14Name1411
15Name1576
16Name1698
17Name1743
18Name1810
19Name1922
20Name2045
21Name2155
22Name2245
23Name2367
24Name2490
25Name2555
26Name2675
27Name2790
28Name2890
29Name292
30Name3085

 

Message 3 of 8
803 Views
0

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

View All
Top Solution Authors
View All