Forum Discussion

ashmitp869's avatar
ashmitp869
Responsive Resident
1 year ago
Solved

dax expression help needed

Hi All, Help me with a measure- Current dax Forecast to complete = VAR FC_Terminal = SUMX( FILTER( CostItem, CostItem[Is_terminal] = TRUE() ), ...
Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi ashmitp869 ,

     

    Please try below DAX measure:

    Forecast_to_complete_test_1 =

    VAR ForecastMethod_CurrentRow =

        SELECTEDVALUE(CurrentProjectForecastDetail[Forecast_Method], "None")

    VAR RowValue =

        IF(

            ForecastMethod_CurrentRow = "None",

            0,

            [Remaining Qty] * [CE Rate]

        )
    RETURN

    IF(

        ISINSCOPE(CostItem[Cost_item_id]),

        RowValue,

        SUMX(

            FILTER(

                CostItem,

                CostItem[Is_terminal] = TRUE()

                    && RELATED(CurrentProjectForecastDetail[Forecast_Method]) <> "None"

            ),

            [Remaining Qty] * [CE Rate]

        )

    )

     

    If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it!

    Regards,

    B Manikanteswara Reddy

Anonymous's avatar
Anonymous
Not applicable
1 year ago

Hi ashmitp869 ,

 

Thank you for reaching out to Microsoft Fabric Community Forum

 

Ashish_Mathur Jihwan_Kim Thank you for your quick responses.

 

Please try the below DAX measure.

 

Forecast to complete (Final) =
VAR FC_Terminal =
    SUMX(
        FILTER(
            ADDCOLUMNS(
                CostItem,
                "ForecastMethod", SELECTEDVALUE(CurrentProjectForecastDetail[Forecast_Method], "None")
            ),
            [Is_terminal] = TRUE() &&
            [ForecastMethod] <> "None"
        ),
        [Remaining Qty] * [CE Rate]
    )
VAR ForecastMethod_CurrentRow =
    SELECTEDVALUE(CurrentProjectForecastDetail[Forecast_Method], "None")
RETURN
IF(
    SELECTEDVALUE(CostItem[Is_terminal]) = FALSE(),
    FC_Terminal,
    IF(
        ForecastMethod_CurrentRow = "None",
        0,
        [Remaining Qty] * [CE Rate]
    )
)
 

If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it!

Regards,

B Manikanteswara Reddy

ashmitp869's avatar
ashmitp869
Responsive Resident
1 year ago

Hi Anonymous 
I tried your expression.

It is showing 0 when Forecast method is None but the total is not summing up 
it should give a result of amount 1,316,029

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi ashmitp869 ,

     

    Please try below DAX measure:

    Forecast_to_complete_test_1 =

    VAR ForecastMethod_CurrentRow =

        SELECTEDVALUE(CurrentProjectForecastDetail[Forecast_Method], "None")

    VAR RowValue =

        IF(

            ForecastMethod_CurrentRow = "None",

            0,

            [Remaining Qty] * [CE Rate]

        )
    RETURN

    IF(

        ISINSCOPE(CostItem[Cost_item_id]),

        RowValue,

        SUMX(

            FILTER(

                CostItem,

                CostItem[Is_terminal] = TRUE()

                    && RELATED(CurrentProjectForecastDetail[Forecast_Method]) <> "None"

            ),

            [Remaining Qty] * [CE Rate]

        )

    )

     

    If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it!

    Regards,

    B Manikanteswara Reddy