powerbi

Power BI How to set up data/measure to get averages at two different granularities in a Matrix visualization


This may be a duplicate question, but I am not sure of the correct wording to find the answer:

So the cleanest way I can think to ask this is: How do I get the average daily number of occurrences at a service level AND the average number of occurrences at a department level (There are multiple services in a department) in one matrix vis in Power BI?

I have a hierarchical dataset that I am trying to use to get the monthly averages. Departments are the higher level of Services. On the lower row (Service, I want it to display the Average daily occurrences, but on the Department level, it needs to be the sum of occurrences for all services that day, and then averaged.This is what it is doing currently:

example of services feeding rows

It seems to be the answer: "The average on a service level." So instead of 10.02, it should be 18.13 on the Department Level. I verified this by building a 2nd table where I removed service, grouped by EVENT_DT and Department, and summed the Number of Occurrences.

How do I set up the data/matrix (do I need to create a measure?) to achieve this? The data currently is all in one table.

The dataset is straightforward, too. The granularity is EVENT_DT + Service:

table that feeds matrix

The closest answer I have found is this link: https://community.fabric.microsoft.com/t5/Desktop/Matrix-sum-at-lower-level-and-Average-at-Higher-level/td-p/1822545

But I just can't seem to figure out how to get it to average at a Department level; and it duplicates over months.


Solution

  • For daily average, try a Measure similar to:

    Avg Daily Patients = 
      AVERAGEX(
        DISTINCT('YourTable'[EVENT_DT]),
        CALCULATE( SUM('YourTable'[Number of Occurances]) )
      )
    

    This should work for any level of your Matrix.