Using a measure in Y axis

[Interact]

Hello everyone, I need some help.

I have a database with a hierarchy of Units, Processes, and Risks. Each Risk can have a different Result outcome according to its hierarchy, and I classify it as LOW, NORMAL, and HIGH (measure "AVG Label") based on the filter defined by the user.

What I need is a chart (similar to the example in the image), showing distinct count risks and their classification, according to the selected filter. However, I'm not finding a way to create this chart.

[Anonymous · Accepted solution]

Hi @Interact ,
Here some steps that I want to share, you can check them if they suitable for your requirement.
Here is my test data:

Create a measure

Average by Risk = 
VAR _count =
CALCULATE(
    COUNT('Table'[Risk]),
    ALLEXCEPT(
        'Table',
        'Table'[Risk]
    )
)
VAR _sum = 
CALCULATE(
    SUM('Table'[AVG]),
    ALLEXCEPT(
        'Table',
        'Table'[Risk]
    )
)
RETURN
_sum/_count

Create two columns

Average Label risk = SWITCH(TRUE(),
                    [Average by Risk]>0 && [Average by Risk]<=2, "LOW",
                    [Average by Risk]>2 && [Average by Risk]<=4, "NORMAL",
                    [Average by Risk]>4,"HIGH"
)

Count = 
VAR _distinctcount = 
CALCULATE(
    DISTINCTCOUNT('Table'[Risk]),
    FILTER(
        'Table',
        'Table'[Average Label risk] = EARLIER('Table'[Average Label risk])
    )
)
VAR _count = 
CALCULATE(
    COUNT('Table'[Average Label risk]),
    ALLEXCEPT(
        'Table',
        'Table'[Average Label risk]
    )
)
RETURN
_distinctcount/_count

Final output

Because neither the x-axis nor the y-axis can be categorized in power bi using MEASURE as a value, but only COLUMN

Best regards,

Albert He

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[Anonymous]

Create a Count measure - HighLowCount = Count(tablename[columnname]) and place on the same visual as the High Low measure.

[Interact]

Hello, thanks for your help!

But there's one more point, in the chart I need to count each Risk only once, and its classification should be the average of its Results.

In the example, RISK-001 should be 4 = NORMAL ((2 + 4 + 6)/3 = 4).