Weighted average incorrect total

[keviwilso]

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

[v-yuezhe-msft]

@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

[keviwilso · Accepted solution]

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.

[mattbrice]

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]

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