Forum Discussion

dieffen's avatar
dieffen
Frequent Visitor
3 years ago

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 aver...
Need Help
dieffen's avatar
dieffen
Frequent Visitor
3 years ago

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's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    3 years ago

    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.

    • dieffen's avatar
      dieffen
      Frequent Visitor
      3 years ago

      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....