Forum Discussion
Matrix different value calculation by each row category by month
I need to create a report that has different metric row categories ( total patient days, avg cenus, ER Visits, etc) where in most cases the value needs to be summed but sometimes it needs to be averaged (avg census). Then the columns would contain the month. Each of category data comes in as a separate data source and have a field indicating the category type. I took the 3 tables and appended to a single table in PBI which becomes the source of the metric below. The example shows 3 categories but there would be a dozen or more that I would need to add. Is there a better approach or visualization which easily scales where depending on the category a different summarization function could be used (sum / avg)
Appreciate any help,
Eric
4 Replies
- Greg_Deckler
Community Champion
dieffen Maybe: Matrix Measure Total Triple Threat Rock & Roll - Microsoft Power BI Community
Or: First, please vote for this idea: https://ideas.powerbi.com/ideas/idea/?ideaid=082203f1-594f-4ba7-ac87-bb91096c742e
This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376
Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907
Also: https://youtu.be/uXRriTN0cfY
And: https://youtu.be/n4TYhF2ARe8 Appreciate the reply but it's not a Total / subtotal issue....Actually I should have removed that from the illustration. The category provides the context of the value which each category has different types of data where a Total wouldn't make sense.
- Greg_Deckler
dieffen Right, which is why I led with MM3TR&R. It's really hard to understand your requirement but it sounds like for different categories you want different aggregations, which is effectively what MM3TR&R is doing which is different aggregations at different levels. In your case you just need to get the MAX of your "Metric" and depending on what it is, do a SUMX or AVERAGEX or whatever. Probably a SWITCH statement. Again, super hard to understand your exact requirements.
Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882
Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.Greg_Deckler , below is example data:
Record_Type, date, value (field names)
Resident_Days, 1/1/2023, 52
Resident_Days, 1/2/2023, 48
ER_visits, 1/2/2023, 2
Daily_Census, 1/1/2023,52
Daily_Census, 1/2/2023,48
The desired output would be:
Record_Type Jan
Resident_Days 100
ER_Visits 2
Daily_Census 50
The Resident_Days and ER_Visits is a sum of all the days and visits in the month and the Daily_Census is the daily average. i have the records joined to a date dimension table to get the month. The idea is to add metrics by having at least the date and value and the record type would provide the context of the value. All the metrics get appended to a sing "table" by the append query.... It seems to work if the calculations are all sum or all average but not if there are different calculations. Open to a better approach but was hoping to not have to create a bunch of matrices....I didn't show in the data but the there is also a Location field where they can expand the metric and see the numbers at to location level as well. This would expand the matrix on top of a matrix that would be placed right below to say show avg versus sum.
Thanks....
