Measure to calculate average with hierarchy in matrix across columns and rows

[pavloshlapa]

Dear Experts,

I have a dataset:

Each Variable, except Value also present as a filter

There is a matrix created:

This is not a simple Matrix as Country comes from another Calculated Table to provide me with 2 static columns AVG and MAX + as many countries, as will be entering the dataset (currently there are only CA & DE)

AVG and MAX values come from 2 measures:

AVG:

Average Actual Value = 
SUMX(
    VALUES('Main Table'[KPI]),
    CALCULATE(
        AVERAGE('Main Table'[Value]),
        FILTER('Main Table', 'Main Table'[Value_Type] = "Actual")
))

MAX:

Max Actual Value = 
SUMX(
    VALUES('Main Table'[KPI]),
    CALCULATE(
        MAX('Main Table'[Value]),
        FILTER('Main Table', 'Main Table'[Value_Type] = "Actual")
))

Measure that is used in Values section of the matrix:

Measure = 
VAR calc =
    IF(SELECTEDVALUE('Custom Hierarchy'[Index]) = 2, [Average Actual Value],
    IF(SELECTEDVALUE('Custom Hierarchy'[Index]) = 1, [Max Actual Value],
    [Actuals]
    ))

RETURN
IF(ISBLANK([Actual Value]), BLANK(), calc)

On the lowest level (Apple, Banana, Mango ...) there is no problem at all. AVG & MAX values properly calculated using input for DE & CA.

The problem occurs for MAX Measure on parental levels, like Fruits & Veggies

As you can see, on aggregate level AVG still is correct, but MAX does not deliver the correct result:

It should be:

-> Veggies = 57

-> Fruits = 54

I would appreaciate it, if anyone could give me a hint on what could be possibly wrong with MAX Measure.

Sincerely,

Pavlo

[v-karpurapud]

Hi @pavloshlapa 

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[pavloshlapa]

Hi @v-karpurapud Unfortunately it did not. A MAX Measure should be able to return maximum values on aggregate level for Fruits & Veggies between CA & DE. And in case of provided solution it aggregates column values of these categories.

[v-karpurapud]

Hi @pavloshlapa 

Please try withe below DAX:

Measure =
VAR SelectedMetric =

Measure =
VAR SelectedMetric =
SELECTEDVALUE('Custom Hierarchy'[Index])

RETURN
SWITCH (
TRUE(),
SelectedMetric = 2, [Average Actual Value],
SelectedMetric = 1, [Max Actual Value],
[Actuals]
)

If this is also not working, please share the .PBIX file. It will help us understand the issue clearly and provide you with better assistance.

If this post helps, kindly mark it as Accepted Solution.

Thank You!

[pavloshlapa]

@v-karpurapud Same result. I will provide the PBIX, hope this will help.

[v-karpurapud · Accepted solution]

Hi @pavloshlapa 

Thank you for update. Please share PBIX. We are ready to assist you.

[v-karpurapud]

Hi @pavloshlapa 

It's been a while since we last heard from you. We are ready to assist you with resolving the issue, but we need the necessary details from you. Kindly share the .PBIX so we can better understand and address your issue.

Thank You.

[v-karpurapud]

Hi @pavloshlapa 

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.

Thank you.

[v-karpurapud]

Hi @pavloshlapa 

Thank you for reaching out to the Microsoft Fabric Community Forum.
 

Regarding your query on creating a measure to calculate average with hierarchy in matrix across columns and rows

I have created a sample .pbix file to demonstrate one possible solution that aligns with your goal of making custom-calculated totals appear correctly in a Power BI matrix, using a custom layout table like 'Custom Hierarchy' for presenting AVG, MAX, and Country-level values.Please review them to see if this approach meets your reporting requirements.

If this response resolves your query, kindly mark it as Accepted Solution to help other community members. A Kudos is also appreciated if you found the response helpful.
 

Thank You!