dax expression help needed

[ashmitp869]

Hi All,
Help me with a measure-
Current dax

Forecast to complete = 
VAR FC_Terminal =
    SUMX(
        FILTER(
            CostItem,
            CostItem[Is_terminal] = TRUE()
        ),
        [Remaining Qty] * [CE Rate]
    )

RETURN
IF(
    SELECTEDVALUE(CostItem[Is_terminal]) = FALSE(),
    FC_Terminal,
    [Remaining Qty] * [CE Rate]
)

How to modify the dax to include the logic -
when Forecast_method is None don't do [Remaining Qty] *[CE Rate] OR show 0
else
do the multiply

Forecast_to_complete_test = 
VAR FC_Terminal =
    SUMX(
        FILTER(
            CostItem,
            CostItem[Is_terminal] = TRUE()
        ),
        [Remaining Qty] * [CE Rate]
    )
VAR ForecastMethod =
    FIRSTNONBLANK(CurrentProjectForecastDetail[Forecast_Method], 1)

RETURN
IF(
    SELECTEDVALUE(CostItem[Is_terminal]) = FALSE(),
    FC_Terminal,
    IF(
        ForecastMethod <> "None",
        FC_Terminal,
        0
    )
)

I tried this , but not working

[Ashish_Mathur]

Hi,

Share the download link of the PBI file.  Explain the problem in simple English.  Show the expected result clearly.

[ashmitp869]

Hi @Ashish_Mathur 

Sample file provided.
https://github.com/suvechha/samplepbi/blob/main/sample%20(2)%20-%20Copy.pbix

Expected result 
When Forecast method is None show 0.
And
total Sum should be 1,316,029 instead of 1,951,120

Thanks

[Ashish_Mathur]

Hi,

Used these measures

Measure 2 = [Remaining Qty] * [CE Rate]

Measure 4 = MAX(CurrentProjectForecastDetail[Forecast_method])

Measure 3 = SUMX(FILTER(VALUES(CostItem[CBS Level 4]),[Measure 4]<>"None"),[Measure 2])

Hope this helps.

[ashmitp869]

May be I am getting that in that line, but how to work it out in terminal total
The Terminal is 1.5.1 Project Managent Team with 1,951,120 it should be 1,316,029

[v-bmanikante]

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]

Hi @v-bmanikante 
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

[v-bmanikante · Accepted solution]

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

[Jihwan_Kim]

Hi,

I am not sure how your semantic model looks like, but please try something like below whether it works.

expected result measure: =
SUMX (
    SUMMARIZE (
        fact_table,
        dim_table[column_in_matrix_01],
        dim_table[column_in_matrix_02]
    ),
    [your_fixed_measure]
)