Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
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).
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] ) )
Ok - got it. But the standalone measures when put into the matrix visual do work, correct?
Hello JirkaZ,
Yes, the standalone measures work, but when combined with the Calculation Groups of Gross Margin % the Variance and % Change don't work correctly.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
94 | |
82 | |
66 | |
58 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |