Search code examples
powerbiaveragedaxmeasure

How to create a successive measures in Power BI

I'm trying to create a three successive measures. In this case, I have two levels of grouping finding maximum, then the third level of grouping I'd like to have the average. This needs to be a measure as I need to be able to slice the inputs of the aggregation.

I'm new to Power BI, so I may have not written the syntax correctly. I've tried various iterations of averagex, summarize, groupby etc.

All help is much appreciated!

Here is an example of the data I am working with:

Name uniqueID Equipment_Number Failure_Mode Rating A A1 A1A Succcess 1 A A1 A1A Low 2 A A1 A1B Succcess 1 A A1 A1B Success 1 A A2 A2A Succcess 1 A A2 A2A High 4 A A2 A2B High 4 A A2 A2B High 4
B B1 B1A Succcess 1 B B1 B1A Succcess 1 B B1 B1B Succcess 1 B B1 B1B High 4 B B2 B2A Low 2 B B2 B2A Success 1 B B2 B2B Medium 3 B B2 B2B Low 2

I'd like to have three measures:

Three successive measures:
using the Rating Data from the table above
    Equipment_Number Max
    A1A  2
    A1B  1
    A2A  4
    A2B  4
    B1A  1
    B1B  4
    B2A  2
    B2B  3

   using the Equipment_Number max measure grouping by uniqueID
    uniqueID Max
    A1  2
    A2  4
    B1  4
    B2  3

    using the uniqueID max measure grouping by Name
    Name Average
    A  3
    B  3.5
Solution

  • This is because when you define a variable, it's a fixed value. I.e., __Module_Rating_Measure is fixed.

    Then your second VAR, __System_Rating_Measure gives the same constant since every value you're maxing over is the same fixed value. Same for the average.

    Try defining those VAR parts as separate measures rather than within the same measure as variables.

    The other option would be to use SUMMARIZE to build table variables rather than scalar variables.

    For more specific detail, it would help to edit your question to provide sample data and desired result.

    Edit: I don't see any reason to take the max twice. Just take the max once at the uniqueID level and then average:

    Average =
AVERAGEX (
    VALUES ( Table1[uniqueID] ),
    CALCULATE (
        MAX ( Table1[Rating] ),
        Table1[uniqueID] = EARLIER ( Table1[uniqueID] )
    )
)

    This iterates through each uniqueID value in the current filter context and calculates the maximum Rating for that ID and then takes the average.

    AvgMax