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);
Num Calc Item:
Forecast Num =
VAR SelectedBC = SELECTEDVALUE('Budget Code'[Budget Code])
VAR NMonths = SWITCH(SelectedBC, "Reforecast 3+9", 3, "Reforecast
If this answer helped, please click 👍 or Accept as Solution.
-Kedar
LinkedIn: https://www.linkedin.com/in/kedar-pande