Forum Discussion

bilaali's avatar
bilaali
Frequent Visitor
3 years ago

Calculation Group - Gross Margin %

Hello,

Wondering if anyone can help me with a calculation group discrepancy, I’m encountering with formulating Gross Margin % when utilizing Variance and % Change

The Gross Margin % within Tabular Editor:

 

VAR Rev =
    CALCULATE ( SELECTEDMEASURE (), 'GL Account'[LOD 3] = "REVENUE" )
VAR GM =
    CALCULATE (
        SELECTEDMEASURE (),
        'GL Account'[LOD 3] = "REVENUE"
            || 'GL Account'[LOD 3] = "COST OF REVENUE"
    )
RETURN
    IF ( ISTEXT ( SELECTEDMEASURE () ), SELECTEDMEASURE (), DIVIDE ( GM, Rev ) )

 

and the results should be

However, it is displaying it the results

The other formulas

 

Variance = 
VAR _Actuals = 'GL Actuals'[Actuals]
VAR Actuals_PY = 'GL Actuals'[Actuals PY]
RETURN
    IF (
        NOT ISBLANK ( _Actuals ) && NOT ISBLANK ( Actuals_PY ),
        _Actuals - Actuals_PY
    )
% Change = 
VAR _Actuals = 'GL Actuals'[Actuals]
VAR Actuals_PY = 'GL Actuals'[Actuals PY]
VAR Diff = _Actuals - Actuals_PY
VAR Result =
    IF (
        ISBLANK ( _Actuals ) && ISBLANK ( Actuals_PY ),
        BLANK (),
        IF (
            Actuals_PY > 0,
            DIVIDE ( Diff, Actuals_PY ),
            IF ( Actuals_PY = 0, 0, - DIVIDE ( Diff, Actuals_PY ) )
        )
    )
RETURN
    Result

 

 

Wondering if anyone can help?

 

Kind Regards

 

Need Help

4 Replies

  • JirkaZ's avatar
    JirkaZ
    Icon for Solution Specialist rankSolution Specialist
    3 years ago

    I can see immediately that the _Actuals and Actuals_PY variables don't use any form of aggregation. At the same time I assume those are referencing a column (not a measure). 
    So you have to implement some sort of aggregation (a SUM probably).

    • bilaali's avatar
      bilaali
      Frequent Visitor
      3 years ago

      Hello JirkaZ,

      Thank you for your prompt response.

       

      The Actuals & Actuals PY are measures

      Actuals = SUM ( 'GL Actuals'[Amount] )
      Actuals PY = CALCULATE ( 'GL Actuals'[Actuals], SAMEPERIODLASTYEAR ( 'Calendar'[Date] ) )

       

      • JirkaZ's avatar
        JirkaZ
        Icon for Solution Specialist rankSolution Specialist
        3 years ago

        Ok - got it. But the standalone measures when put into the matrix visual do work, correct?