cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
JoRo50
Frequent Visitor

calculation to show value for NOT variable

Hello, 

I'm trying to create a measure to show a calculation for values other than the variable indicated in the row label.  For instance, in the data below (Table), I want to be able to do a calculation for X, but also show the calculation for all NOT-X,  Calculate for Y and show all NOT-Y, etc.

JoRo50_0-1658938663995.png   Ideally, my final table would be something like this, with the potential to add filters.

 

JoRo50_1-1658938854279.png  In this table, the Ave(not Variable) for X is calculating the average of Y and Z.  Any help would be appreciated.  Let me know if there is anything I should clarify.

Thank you,

J

 

 

1 ACCEPTED SOLUTION
AUaero
Responsive Resident
Responsive Resident

Using the data table you provided, you can create two measures to get the desired result:

# Avg Value = AVERAGE(FilterTable[Value])
# Avg Not Value = 
VAR ThisVariable = MAX(FilterTable[Variable])

RETURN
CALCULATE(
    [# Avg Value],
    FILTER(
        ALL(FilterTable),
        NOT(FilterTable[Variable] = ThisVariable)
    ),
    VALUES(FilterTable[Filter])
)

Here are the results when no Filter slicer is applied:

AUaero_0-1658940849395.png

And with the Filter slicer:

AUaero_1-1658940883708.png

Please accept this as the solution if this solves your problem.

 

 

View solution in original post

2 REPLIES 2
AUaero
Responsive Resident
Responsive Resident

Using the data table you provided, you can create two measures to get the desired result:

# Avg Value = AVERAGE(FilterTable[Value])
# Avg Not Value = 
VAR ThisVariable = MAX(FilterTable[Variable])

RETURN
CALCULATE(
    [# Avg Value],
    FILTER(
        ALL(FilterTable),
        NOT(FilterTable[Variable] = ThisVariable)
    ),
    VALUES(FilterTable[Filter])
)

Here are the results when no Filter slicer is applied:

AUaero_0-1658940849395.png

And with the Filter slicer:

AUaero_1-1658940883708.png

Please accept this as the solution if this solves your problem.

 

 

JoRo50
Frequent Visitor

@AUaero Is there a solution if I am being asked to add a second filter (Filter2) to this table? With 2 filters, the Not Value calculation should still calculate based on the values remaining after the filters have been selected.

 

(I also noticed that my example for Filter B, Variable Y should have an Avg Not Value of 18.5.  Thank you for correcting that oversight.)

 

Thank you.

Helpful resources

Announcements
Winner of T-Shirt Design

Power BI T-Shirt Design Challenge 2023

Find out who won the T-Shirt Design Challenge and the top 3 finalists.

March 2023 Update3

Power BI March 2023 Update

Find out more about the March 2023 update.