cancel
Showing results 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.

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.

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

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
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:

And with the Filter slicer:

2 REPLIES 2
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:

And with the Filter slicer:

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.

Announcements

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

Fabric Community Update - April 2024

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

Power BI Monthly Update - March 2024

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

Top Solution Authors
Top Kudoed Authors