cancel
Showing results for
Did you mean:
Highlighted
Frequent Visitor

## Weighted average incorrect total

Hi

I am trying to replicate the Weighted ABM calculating defined in the below Excel screenshot in DAX.

Weighted ABM is derived at the Project Level first and then simply aggregated SUM to derive the Weighted ABM at different grains.

Weighted ABM = Weighting * ABM

Weighting = Fee Revenue / Total Fee Revenue for ALL Selected

Total Weighted ABM = SUM( Weighted ABM)

Sample Excel Data Set

My first version of the DAX equivalent is as follows:

```V1_Weighting ABM =

DIVIDE (

SUM ( 'project-as-sold-marg-perc'[Fee Revenue] ),

CALCULATE (

SUM ( 'project-as-sold-marg-perc'[Fee Revenue] ),

ALLSELECTED ()

)

)

* AVERAGE ( 'project-as-sold-marg-perc'[ABM] )```

Which results in the correct Weighted ABM at the row level but the Total level is wrong as it applies the same formula at the aggregated level rather than sum the derived value first for each row and then aggregating.

This led me to changing the DAX to wrap the calculation using a SUMX:

```V2_Weighted ABM =

SUMX (

VALUES ( 'project-as-sold-marg-perc'[Project Code] ),

CALCULATE (

DIVIDE (

SUM ( 'project-as-sold-marg-perc'[Fee Revenue] ),

CALCULATE (

SUM ( 'project-as-sold-marg-perc'[Fee Revenue] ),

ALLSELECTED ('project-as-sold-marg-perc')

)

)

* AVERAGE ( 'project-as-sold-marg-perc'[ABM] )

)

)```

Which results in the INCORRECT Weighted ABM at the row level but the CORRECT Total.

Any suggestions on how to change the DAX so I can achieve the correct results at the row level and also at the total level would be greatly appreciated.

Note this measure will be used in ad-hoc analysis where the user may just drag the measure on with no other attributes but just filters. I have seen similar posts where it has suggested using the HASONEVALUE function but I would need to check for any attribute that could be dragged on?

Cheers,

Kevin

1 ACCEPTED SOLUTION

Accepted Solutions
Frequent Visitor

## Re: Weighted average incorrect total

The solution was actually just V2_Weighted_ABM measure in the end. at a project level from a user reporting perspective they just want to see the project as sold margin perc and not the weighted one which does not mean anything - it is only used in the aggregation to the visual totals.

With combining them (which is no longer required) I did get that working with using the HASONEVALUE function and checking the project code.

Thanks all for your help and advice with this. It is greatly appreciated.

4 REPLIES 4
Senior Member

## Re: Weighted average incorrect total

You don't say whether the Project code is unique per row and per customer in your data table, but assuming it is I would do the following measures:

```Weighting Measure =
VAR _thisRevenue =
CALCULATE ( SUM ( 'project-as-sold-marg-perc'[Fee Revenue] ) )
VAR _allSelectedRevenue =
CALCULATE (
SUM ( 'project-as-sold-marg-perc'[Fee Revenue] ),
ALLSELECTED ( 'project-as-sold-marg-perc'[Project Code] )
)
RETURN
DIVIDE ( _thisRevenue, _allSelectedRevenue )```

Then for Weighted ABM :

```Weighted ABM Measure =
VAR _thisABM =
CALCULATE ( SUM ( 'project-as-sold-marg-perc'[ABM] ) )
RETURN
SUMX ( VALUES ( 'project-as-sold-marg-perc'[Project Code] ),
_thisABM * [Weighting Measure]
)```

But you are right that while DAX is awesome, it isn't infinitely flexible without some serious state checking so some basic assumptions about what is on the rows/columns of a visual ofter have to be made.  Mine assumes "Project Code" will be in visual somehow.

Frequent Visitor

## Re: Weighted average incorrect total

Hi Matt

In the data set the project code will not be unique. It is a Fact project transaction table which will have a row per project, project task, client, date, revenue . The Project ABM is unque by project which is stored on the Project dimension.

I have applied your suggested DAX however it results in the following (Measures Prefexed with V5_)

As you can see the total is still unforuntatley not correct. Also when I filter the data set for a couple of projects the weigthing is still calculated across the total revenue and not for the ALLSELECTED data set.

The current visuals where this measure will be applied Project code will not be used, rather attributes from the Client Dimension - Client Type, Client Market, Client Geography, Client - A different visual for each of the attributes.

Keen to know if you have any more suggestions.

Cheers

Kevin

Moderator

## Re: Weighted average incorrect total

Create measure using DAX below and check if it returns your expected result.

`Measure = IF(COUNTROWS(VALUES('project-as-sold-marg-perc'[Project Code]))=1, [V1_Weighting ABM],SUMX(VALUES('project-as-sold-marg-perc'[Project Code]),[V1_Weighting ABM]))`

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Frequent Visitor

## Re: Weighted average incorrect total

The solution was actually just V2_Weighted_ABM measure in the end. at a project level from a user reporting perspective they just want to see the project as sold margin perc and not the weighted one which does not mean anything - it is only used in the aggregation to the visual totals.

With combining them (which is no longer required) I did get that working with using the HASONEVALUE function and checking the project code.

Thanks all for your help and advice with this. It is greatly appreciated.

Announcements

#### Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

#### Community News & Announcements

Get your latest community news and announcements.

#### Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

#### Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Top Kudoed Authors
Users Online
Currently online: 288 members 3,267 guests
Recent signins: