Forum Discussion

MamaHani2's avatar
MamaHani2
Frequent Visitor
7 months ago
Solved

calculation item from calculation group not working correctly

hello i have a problem with the correct dax formula for the calculation item that i need; i have a source tableA; this table contains records of actuals and budget (RecordType); i have 6 other colu...
  • MamaHani2's avatar
    MamaHani2
    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);