Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

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
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

March 2024 PBI Gallery Image

Power BI Monthly Update - March 2024

Check out the March 2024 Power BI update to learn about new features.