Forum Discussion
How to get average CB PVs by PMonth?
Hello,
I have the following tables
I want to get the average CB PVs per meta ID by PMonth ... it works with the calendar hierarchy and doesn’t with both PMonth and PDate ... why? How to get it working?
1.meta
|| ID | PMonth | PDate ||
1 | 2010 | Thursday, October 1, 2020
2 | 2011 | Sunday, November 1, 2020
3 | 2111 | Monday, November 1, 2021
4 | 2112 | Wednesday, December 1, 2021
2.cb
|| ID | Date | PVs ||
2 | Thursday, October 1, 2020 | 1
2 | Sunday, November 1, 2020 | 1
1 | Wednesday, December 1, 2021 | 3
3 | Wednesday, December 1, 2021 | 1
3.ID_table
|| ID ||
1
2
3
4
5
4.Calendar
|| Date | year | year-month ||
Sample PBIX
4 Replies
- amitchandak
Super User
SA2 , do need sum of PV and then avg like ?
Avergaex(values('Date'[Month Year]), calculate(Sum(CV[PV]) ) )
amitchandak
Trying this formula is fine with calendar hierarchy as before, however it is still not working with PMonth
johnt75Try changing the relationship between id table and meta to single rather than bidirectional. As it stands any filters on the meta table are passing to the cb table both through the calendar table and the id table, which could introduce ambiguity.
