- Forums
- Get Help with Power BI
- Desktop
- Service
- Report Server
- Integrations with Files and Services
- Mobile Apps
- Developer
- Let's Talk Data
- Custom Visuals Development Discussion
- Community Support
- Welcome to the Community
- Community Feedback
- Community Help Blog
- Training and Consulting
- Dashboard in a Day
- EdX Specific Training Discussion Forum

Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Microsoft Power BI Community
- Forums
- Get Help with Power BI
- Desktop
- Weighted average incorrect total

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

Highlighted

keviwilso

Frequent Visitor

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-17-2018
08:19 PM

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)

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

Solved! Go to Solution.

Report Inappropriate Content

Message 1 of 5

1 ACCEPTED SOLUTION

Accepted Solutions

keviwilso

Frequent Visitor

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-25-2018
07:52 PM

Thanks for the reply.

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

mattbrice

Senior Member

Re: Weighted average incorrect total

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-17-2018
09:18 PM

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.

keviwilso

Frequent Visitor

Re: Weighted average incorrect total

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-17-2018
09:53 PM

Hi Matt

Thanks for your quick reply, it is greatly appreciated.

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

v-yuezhe-msft

Moderator

Re: Weighted average incorrect total

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-19-2018
12:15 AM

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.

If this post

keviwilso

Frequent Visitor

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-25-2018
07:52 PM

Thanks for the reply.

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.