Forum Discussion
calculation item from calculation group not working correctly
- 7 months ago
hello, thank you for your answer; what do you mean with the 'never apply calc items directly to ratio logic'? it seems to me that your forecast measures is exactly as my first attept of the calculation item ; i already have base measures measure1 and measure2; where exactly do i create now the ratio then measure1/measure2? if i create a calculation item with your definition (which i think is ~ my original definition: 2 separate calculates for selectedmeasurs) then if i create a ratio measure measure1/measure2 and drag this to visual under this calculation item it give the issue i described in my post: instead of doing measure1 actual + budget / measrue 2 actual + budget, it does actual measure1/measure2 + budget measure1/measure2; i was playing around with this a bit and i came to conclution the reason for this is because the ratio is being passed on into the calculation group; so the only way to work around this is to have 1 calculate selectedmeasure in my calculation item; after 1 milion trials i came up with this code:
VAR SelectedBC = SELECTEDVALUE('Budget Code'[Budget Code])
VAR NMonths =
SWITCH(
SelectedBC,
"Reforecast 3+9", 3,
"Reforecast 6+6", 6,
"Reforecast 9+3", 9,
0
)
VAR MonthsForActuals = GENERATESERIES(1, NMonths, 1)
VAR CombinedFilterTable =
CALCULATETABLE (
FILTER (
'TableA',
(
'TableA'[Record Type] = "Actuals"
&& RELATED ( 'Date'[Month nr] ) IN SELECTCOLUMNS ( MonthsForActuals, "Value", [Value] )
&& RELATED ( 'Period Type'[Period Type] ) <> "Closing"
)
||
(
'TableA'[Record Type] = "Budget"
&& RELATED ( 'Budget Code'[Budget Code] ) = SelectedBC
&& RELATED ( 'Budget Model'[Budget Model Code] ) = "Final"
)
),
REMOVEFILTERS ( 'Budget Code' ),
REMOVEFILTERS ( 'Budget Model' ),
REMOVEFILTERS ( 'Period Type' )
)
VAR Forecast =
CALCULATE (
SELECTEDMEASURE(),
KEEPFILTERS ( CombinedFilterTable ),
REMOVEFILTERS ( 'Budget Code' ),
REMOVEFILTERS ( 'Budget Model' ),
REMOVEFILTERS ( 'Period Type' )
)
Return Forecast
this seem to be working correctly both on absolute values (i.e. measure 1 forecast and measure2 forecast) as well as on the ratio (measure1 forecast/measure2 forecast);
hello, thank you for your answer; what do you mean with the 'never apply calc items directly to ratio logic'? it seems to me that your forecast measures is exactly as my first attept of the calculation item ; i already have base measures measure1 and measure2; where exactly do i create now the ratio then measure1/measure2? if i create a calculation item with your definition (which i think is ~ my original definition: 2 separate calculates for selectedmeasurs) then if i create a ratio measure measure1/measure2 and drag this to visual under this calculation item it give the issue i described in my post: instead of doing measure1 actual + budget / measrue 2 actual + budget, it does actual measure1/measure2 + budget measure1/measure2; i was playing around with this a bit and i came to conclution the reason for this is because the ratio is being passed on into the calculation group; so the only way to work around this is to have 1 calculate selectedmeasure in my calculation item; after 1 milion trials i came up with this code:
VAR SelectedBC = SELECTEDVALUE('Budget Code'[Budget Code])
VAR NMonths =
SWITCH(
SelectedBC,
"Reforecast 3+9", 3,
"Reforecast 6+6", 6,
"Reforecast 9+3", 9,
0
)
VAR MonthsForActuals = GENERATESERIES(1, NMonths, 1)
VAR CombinedFilterTable =
CALCULATETABLE (
FILTER (
'TableA',
(
'TableA'[Record Type] = "Actuals"
&& RELATED ( 'Date'[Month nr] ) IN SELECTCOLUMNS ( MonthsForActuals, "Value", [Value] )
&& RELATED ( 'Period Type'[Period Type] ) <> "Closing"
)
||
(
'TableA'[Record Type] = "Budget"
&& RELATED ( 'Budget Code'[Budget Code] ) = SelectedBC
&& RELATED ( 'Budget Model'[Budget Model Code] ) = "Final"
)
),
REMOVEFILTERS ( 'Budget Code' ),
REMOVEFILTERS ( 'Budget Model' ),
REMOVEFILTERS ( 'Period Type' )
)
VAR Forecast =
CALCULATE (
SELECTEDMEASURE(),
KEEPFILTERS ( CombinedFilterTable ),
REMOVEFILTERS ( 'Budget Code' ),
REMOVEFILTERS ( 'Budget Model' ),
REMOVEFILTERS ( 'Period Type' )
)
Return Forecast
this seem to be working correctly both on absolute values (i.e. measure 1 forecast and measure2 forecast) as well as on the ratio (measure1 forecast/measure2 forecast);
Hi MamaHani2 ,
Thanks for the update. We are happy to hear that you have resolved the issue. Thanks for sharing the details here.
"Never apply calc items directly to ratio logic" means you can’t put a ratio measure in a visual with a calculation group. A calculation item that uses multiple CALCULATE(SELECTEDMEASURE()) calls will ALWAYS break ratios. calculation group wraps the entire expression of the measure.
If the measure is:
[Ratio] = [Measure1] / [Measure2]
then inside the calc item, SELECTEDMEASURE() is:
([Measure1] / [Measure2])
So below code:
CALCULATE( SELECTEDMEASURE(), FilterA )
+
CALCULATE( SELECTEDMEASURE(), FilterB )
becomes:
([M1A]/[M2A]) + ([M1B]/[M2B])
It's mathematically wrong for forecasting.
Note: Your new code have only ONE CALCULATE(SELECTEDMEASURE()), And Actuals + Budget are combined at the row set level. And Ratio is evaluated after aggregation. That's the reason you got the expected result.
Regards,
Dinesh
- v-dineshya7 months ago
Community Support
Hi MamaHani2 ,
We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. And, if you have any further query do let us know.
Regards,
Dinesh