Forum Discussion

keviwilso's avatar
keviwilso
Frequent Visitor
8 years ago
Solved

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 der...
Need Help
  • keviwilso's avatar
    keviwilso
    8 years ago

    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's avatar
mattbrice
Solution Sage
8 years ago

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's avatar
keviwilso
Frequent Visitor
8 years ago

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