Need Help with measure : Overheads Expenses

[Victor_YU]

Hi all,

Currently, I am working on a PBI Dashboard and having some difficulties creating Compensation Expenses (Structure) & Overheads measures.

Compensation Expenses are calculated monthly (in Excel, each column corresponds to a month) with a filter on "O" in the Structure Column. This amount is multiplied by customer's sales/total sales (%CA Client/CA Total in PBI). I don't know why my measure is giving me the wrong amount. Brigitte customer has 247K total costs, but monthly it does not exceed 4.8K. This measure measure must be linked to a customer. 

Compensation Expenses =

CALCULATE(

    SUM('Payrol 2024'[Montant])*[% CA Client/CA Total],

    'Payrol 2024'[Structure ? ] IN {"O"},

    'Base PtCO GM 2024'[Categorie]="CA")

Overheads are calculated according to a cost center linked to each salesperson and then multiplied by sales/total sales. An exception for the overheads: if the director's cost center corresponds to FGXRSA-FG, FGXRSVT-FG, FGXTR-FG, or FGXTV-FG, then you need to add the costs of the FGXVT-FG cost center multiplied by the ccustomer's sales/total sales * 50%. This measure measure must be linked to a customer. 

Thank you in advance.

Best regards.

[DataNinja777 · Accepted solution]

Hi @Victor_YU ,

To troubleshoot and resolve the issues in your DAX measures for Compensation Expenses and Overheads, we can break them down into their components and examine the logic step by step.

For Compensation Expenses, the current measure calculates the sum of the 'Montant' column filtered by the "O" value in the 'Structure ?' column and by "CA" in the 'Categorie' column. This amount is then multiplied by the '% CA Client/CA Total' measure. If the result for Brigitte’s monthly amount does not exceed 4.8K despite having a total of 247K, it indicates a possible issue in the filter context or the way the multiplication is being calculated. It is important to ensure that the filters applied on 'Structure ?' and 'Categorie' are correctly narrowing the data to the intended scope. Additionally, relationships between tables need to be active and properly configured to propagate context.

To address this, a revised measure for Compensation Expenses can use the SUMX function to perform row-by-row multiplication of 'Montant' and '% CA Client/CA Total' before summing the results. This approach ensures that the calculation respects the filters and relationships between the tables. Here is a possible revised measure:

Compensation Expenses =
CALCULATE(
    SUMX(
        'Payrol 2024',
        'Payrol 2024'[Montant] * [% CA Client/CA Total]
    ),
    'Payrol 2024'[Structure ? ] IN {"O"},
    'Base PtCO GM 2024'[Categorie] = "CA"
)

For Overheads, the measure requires calculating the overhead costs linked to each salesperson's cost center, multiplying these costs by '% CA Client/CA Total', and then adding an exception for specific director cost centers. If the cost center matches specific values (e.g., FGXRSA-FG), an additional calculation must be included by multiplying the costs of the FGXVT-FG cost center by '% CA Client/CA Total * 50%'. This logic can be implemented in DAX by defining separate variables for the base overheads, the director exceptions, and the FGXVT-FG costs, which are then summed together in the final measure.

Here is a potential DAX formula for Overheads:

Overheads =
VAR BaseOverheads =
    CALCULATE(
        SUMX(
            'Overheads Table',
            'Overheads Table'[Cost] * [% CA Client/CA Total]
        ),
        'Overheads Table'[Cost Center] <> "FGXVT-FG"
    )
VAR DirectorException =
    CALCULATE(
        SUMX(
            'Overheads Table',
            'Overheads Table'[Cost] * [% CA Client/CA Total] * 0.5
        ),
        'Overheads Table'[Cost Center] IN {"FGXRSA-FG", "FGXRSVT-FG", "FGXTR-FG", "FGXTV-FG"}
    )
VAR FGXVT_FG_Costs =
    CALCULATE(
        SUMX(
            'Overheads Table',
            'Overheads Table'[Cost] * [% CA Client/CA Total]
        ),
        'Overheads Table'[Cost Center] = "FGXVT-FG"
    )
RETURN
    BaseOverheads + DirectorException + FGXVT_FG_Costs

For both measures, it is critical to validate that the data context is correctly defined. This includes ensuring that the '% CA Client/CA Total' measure accurately reflects the proportion of each customer’s sales to total sales and that it is properly filtered by customer. Additionally, verifying that filters and relationships are propagating correctly within the visual can help identify potential issues. Testing individual components of the measures, such as intermediate calculations for 'Montant' and '% CA Client/CA Total', can also aid in debugging the issue. If the visual or slicer settings are misconfigured, it might also affect the measure’s output.

These revisions and checks should help ensure that your measures produce the expected results for Compensation Expenses and Overheads, correctly linked to each customer. Let me know if further clarification or adjustments are needed.

Best regards,

[Victor_YU]

Hi @DataNinja777 

Thank you for your detailed feedback and analysis regarding the DAX measures for Compensation Expenses and Overheads.

I wanted to confirm that I have reviewed and verified each step of the process to ensure accuracy. For the Overheads calculation, I specifically addressed the issue by creating a new table that includes both the cost centers and analytical centers. This setup ensures a more precise allocation and filtering of the data.

Your suggestions for breaking down the logic and utilizing measures such as SUMX have been instrumental. I’ve applied the recommended approach and validated the context filters, relationships, and intermediate calculations to ensure alignment with the expected outcomes.

If any additional refinement is required, feel free to share your thoughts. I appreciate your support and collaboration throughout this process.

Wishing you joyful holidays and a successful start to the new year!

Best regards,