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,
I have a matrix of values containing weekly total of head count. The matrix is aggregating the total HC from from a table where the data is at granular level.. that is ..each week has data for X no of regions, Y no of Towers so on. What I need is the sum of HC by week on each row,,and average of totals below. I need average (sum of Week 1+Week 2+Week3..so on)/no of weeks disaplyed in table. However, it is showing the sum of the totals and not avarage. when i select average under field aggregation the average shows "1" probably because it is averaging all rows at a granular level and not averaging the sum of each week. Would appreciate you help.
Solved! Go to Solution.
solved this using the below... the logic was to apply a different condtion when the measure saw one week vs more than one week
Hi @shaashn ,
Without your data model, what I can think of is the following metrics, please try
Average =
DIVIDE (
CALCULATE ( SUM ( 'Table'[HC] ), ALLSELECTED ( 'Table' ) ),
CALCULATE ( DISTINCTCOUNT ( 'Table'[week] ), ALLSELECTED ( 'Table' ) )
)
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
You should be using measures instead of implicit aggregations. (best practice!)
So... create a measure for the sum:
Sum HC = SUM (Table [HC])
Then another for the average
Average = AVERAGEX (table, [Sum HC])
and finally for the visual:
Final = IF(ISINSCOPE (Table[ Week]), [Sum HC], [Average])
If unproductive, please provide sample data or PBIX file
Proud to be a Super User!
Paul on Linkedin.
Ho shaashn
Try this and please leave kudos.
Create a calendar table with contiguous dates or weeks (contiguou means with no missing gaps).
There are lots of Youtube videos that will show you how to create a list of weeks or dates.
Create a 2 relationship between the calendat table and your X and Y tables.
Then use the calenadt week instead of the X / Y dates in your reports.
This will force 1 row per week as required.
solved this using the below... the logic was to apply a different condtion when the measure saw one week vs more than one week
Hi @shaashn ,
Glad you can find a solution. Please mark your own reply as a answer, more people will benefit.
Best Regards,
Stephen Tao
I initially tried that. there was a 1-1 relationship where "week 1" related to "WK-1 1st jan - 7th jan" (example)
but that didnt work out
Calendars should have 1:M relationship with fact tables.
It will work. There are lots of calendar tutorials on You tube.
If you still need help then create some test data and save it on a share drive and post the link so we can help you.
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 |
---|---|
117 | |
104 | |
77 | |
73 | |
52 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |