cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

New Member

## Power BI Dax Formula - How to calculate a result based visualization selection

Hello, scenario is on my visualization in Power BI, I have a drop down filter selection for customer, if a customer is selected, all data on visual updates for that customer only as expected.  However, I need to create a measure comparing the customer result vs. the global environement to which that customer comes from.  Example, that customer has 50 units.  Others have more or less.  I have created bands of units and assigned a numeric index as a calculated column in the query.  Example 1 - 50 units = 1; 51 - 100 units = 2 and so on, so that each customer is assigned an index of the group they fall into.  Now I need to create a calculation that compares that customer's specific average maintenance costs against the average maintenance costs of the group that the customer falls into.   I am able to calculate the average maintenance from all groups and display on the same visualization as the selected customer using the below measure formula:

Comparitive Maint Average = CALCULATE(
AVERAGEA('fDataTable'[Avg. Monthly Maint.]),
ALL(dClient[Name])
)

So it's:  If the customer displayed = unit index 1 (1 - 50 units condo) then calculate the average maintenance of all customers that belong to the index 1 band and display this result while ignoring the current visualization filter of customer.

Everytime the customer filter selection changes, the unit index has to be derrived so that the calculation for total units is based on the selected customer's unit grouping.

Any ideas?
2 REPLIES 2
Super User

Hi,

Share some data and show the expected result.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Super User

The answer to this depends on what your data structure looks like and where you have stored the group index column. If it is a column on your 'dClient' table you could maybe do something like the following to remove all other filters on the dClient table, but keep the filters on the Group Index column.

Group Maint Average = CALCULATE(
AVERAGEA('fDataTable'[Avg. Monthly Maint.]),
ALL(dClient),
VALUES( dClient[Group Index] )
)

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.