cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
keviwilso Frequent Visitor
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)

 

 ExcelScreenshot.pngSample 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.

 

 DaxV1.png

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.

 

DaxV2.png

 

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
keviwilso Frequent Visitor
Frequent Visitor

Re: Weighted average incorrect total

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.

View solution in original post

4 REPLIES 4
mattbrice Senior Member
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.

keviwilso Frequent Visitor
Frequent Visitor

Re: Weighted average incorrect total

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_)

DaxV5.png

 

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. 

 

DaxV5_Filtered.png

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 v-yuezhe-msft
Moderator

Re: Weighted average incorrect total

@keviwilso,


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.
keviwilso Frequent Visitor
Frequent Visitor

Re: Weighted average incorrect total

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.

View solution in original post

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

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

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

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.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

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

Users Online
Currently online: 238 members 2,328 guests
Please welcome our newest community members: