Solved: Re: Need help with the calculated measure?

Bhaveshp · ‎12-07-2018

Hello,

I have a table as shown below; The code for the measure is correct except the highlighted Values

Logic:

For report = Gross Profit in % of Sales and Header_1 <>Curr.to OEC,

Sales = Gross Profit/Sales

Eg(from image): 2.083/15.534 = 0.134, 4.305/17.431 = 0.247

For report = Gross Profit in % of Sales and Header_1 = Curr.to OEC,

Sales = (Gross Profit in Curr.mth / Sales in Curr.mth) - (Gross Profit in Upd.OEC / Sales in Upd.OEC)

Eg(from image): 0.214 - 0.247 = -0.033 (code is failing here, see the highlighted above)

New Value = 
IF (
    VALUES ( POC[report_param] ) = "Gross Profit in % of Sales" && VALUES ( POC[Header_1] ) <> "Curr.to OEC",
        (
            (
                CALCULATE (
                    [Value Measure],
                    FILTER ( ALL ( POC ), POC[report_param] = "Gross Profit" ),
                    VALUES ( POC[Country] ),
                    VALUES ( POC[Snapshot Date] ),
                    VALUES ( POC[Header_1] ),
                    VALUES ( POC[Header_2] ),
                    VALUES ( POC[project_number] )
                )
                    / (
                        CALCULATE (
                            [Value Measure],
                            FILTER ( ALL ( POC ), POC[report_param] = "Sales" ),
                            VALUES ( POC[Country] ),
                            VALUES ( POC[Snapshot Date] ),
                            VALUES ( POC[Header_1] ),
                            VALUES ( POC[Header_2] ),
                            VALUES ( POC[project_number] )
                        )
                    )
            )
        ),

    IF (
        VALUES ( POC[report_param] ) = "Gross Profit in % of Sales"
            && VALUES ( POC[Header_1] ) = "Curr.to OEC",
            (
                (
                    CALCULATE (
                        [Value Measure],
                        FILTER (
                            ALL ( POC ),
                            POC[Header_1] = "Curr.mth."
                                && POC[report_param] = "Gross Profit"
                        ),
                        VALUES ( POC[Country] ),
                        VALUES ( POC[Snapshot Date] ),
                        --VALUES ( POC[Header_1] ),
                        VALUES ( POC[Header_2] ),
                        VALUES ( POC[project_number] )
                    )
                        / CALCULATE (
                            [Value Measure],
                            FILTER (
                                ALL ( POC ),
                                POC[Header_1] = "Curr.mth."
                                    && POC[report_param] = "Sales"
                            ),
                            VALUES ( POC[Country] ),
                            VALUES ( POC[Snapshot Date] ),
                            --VALUES ( POC[Header_1] ),
                            VALUES ( POC[Header_2] ),
                            VALUES ( POC[project_number] )
                        )
                )
                    - (
                        CALCULATE (
                            [Value Measure],
                            FILTER (
                                ALL ( POC ),
                                POC[Header_1] = "Upd.OEC"
                                    && POC[report_param] = "Gross Profit"
                            ),
                            VALUES ( POC[Country] ),
                            VALUES ( POC[Snapshot Date] ),
                            VALUES ( POC[Region] ),
                            --VALUES ( POC[Header_1] ),
                            VALUES ( POC[Header_2] ),
                            VALUES ( POC[project_number] )
                        )
                            / CALCULATE (
                                [Value Measure],
                                FILTER ( ALL ( POC ), POC[Header_1] = "Upd.OEC" && POC[report_param] = "Sales" ),
                                VALUES ( POC[Country] ),
                                VALUES ( POC[Snapshot Date] ),
                                --VALUES ( POC[Header_1] ),
                                VALUES ( POC[Header_2] ),
                                VALUES ( POC[project_number] )
                            )
                    )
            ),
       [Value Measure]
    )
)

@Anonymous can you look into this and tell me where I am doing wrong here

appreciate your help.

v-lili6-msft · ‎12-10-2018

hi, @Bhaveshp

After my research on your sample pbix file, you could this formula

New Value2 = 
IF (
    VALUES ( POC[report_param] ) = "Gross Profit in % of Sales" && VALUES ( POC[Header_2] ) <> "Curr.to OEC",
        (
            (
                CALCULATE (
                    [Value Measure],
                    FILTER ( ALL ( POC ), POC[report_param] = "Gross Profit" ),
                    VALUES ( POC[Country] ),
                    VALUES ( POC[Snapshot Date] ),
                    VALUES ( POC[Header_1] ),
                    VALUES ( POC[Header_2] ),
                    VALUES ( POC[project_number] )
                )
                    / (
                        CALCULATE (
                            [Value Measure],
                            FILTER ( ALL ( POC ), POC[report_param] = "Sales" ),
                            VALUES ( POC[Country] ),
                            VALUES ( POC[Snapshot Date] ),
                            VALUES ( POC[Header_1] ),
                            VALUES ( POC[Header_2] ),
                            VALUES ( POC[project_number] )
                        )
                    )
            )
        ),

    IF (
        VALUES ( POC[report_param] ) = "Gross Profit in % of Sales"
            && VALUES ( POC[Header_2] ) = "Curr.to OEC",
            (
                (
                    CALCULATE (
                        [Value Measure],
                        FILTER (
                            ALLEXCEPT(POC,POC[project_number]),
                            POC[Header_2] = "Curr.mth."
                                && POC[report_param] = "Gross Profit"
                        ),
                        VALUES ( POC[Country] ),
                        VALUES ( POC[Snapshot Date] ),
                        --VALUES ( POC[Header_1] ),
                        --VALUES ( POC[Header_2] ),
                        VALUES ( POC[project_number] )
                    )
                        / CALCULATE (
                            [Value Measure],
                            FILTER (
                                ALLEXCEPT(POC,POC[project_number]),
                                POC[Header_2] = "Curr.mth."
                                    && POC[report_param] = "Sales"
                            ),
                            VALUES ( POC[Country] ),
                            VALUES ( POC[Snapshot Date] ),
                            --VALUES ( POC[Header_1] ),
                            --VALUES ( POC[Header_2] ),
                            VALUES ( POC[project_number] )
                        )
                )
                    - (
                        CALCULATE (
                            [Value Measure],
                            FILTER (
                                ALLEXCEPT(POC,POC[project_number]),
                                POC[Header_2] = "Upd.OEC"
                                    && POC[report_param] = "Gross Profit"
                            ),
                            VALUES ( POC[Country] ),
                            VALUES ( POC[Snapshot Date] ),
                            VALUES ( POC[Region] ),
                            --VALUES ( POC[Header_1] ),
                            --VALUES ( POC[Header_2] ),
                            VALUES ( POC[project_number] )
                        )
                            / CALCULATE (
                                [Value Measure],
                                FILTER ( ALLEXCEPT(POC,POC[project_number]), POC[Header_2] = "Upd.OEC" && POC[report_param] = "Sales" ),
                                VALUES ( POC[Country] ),
                                VALUES ( POC[Snapshot Date] ),
                                --VALUES ( POC[Header_1] ),
                                --VALUES ( POC[Header_2] ),
                                VALUES ( POC[project_number] )
                            )
                    )
            ),
       [Value Measure]
    )
)

First, Header_1 only has three values (Budget, Forecast, Actuals ), so VALUES ( POC[Header_1] ) <> "Curr.to OEC" is wrong in your formula.

second, be careful for For report = Gross Profit in % of Sales and Header_2 = Curr.to OEC conditional.

FILTER (
ALL ( POC ),
POC[Header_1] = "Curr.mth."
&& POC[report_param] = "Gross Profit"
),
VALUES ( POC[Country] ),
VALUES ( POC[Snapshot Date] ),
--VALUES ( POC[Header_1] ),
VALUES ( POC[Header_2] ),
VALUES ( POC[project_number] )
)

FILTER (
ALL ( POC ),
POC[Header_1] = "Curr.mth."
&& POC[report_param] = "Sales"
),
VALUES ( POC[Country] ),
VALUES ( POC[Snapshot Date] ),
--VALUES ( POC[Header_1] ),
VALUES ( POC[Header_2] ),
VALUES ( POC[project_number] )
)

FILTER (
ALL ( POC ),
POC[Header_1] = "Upd.OEC"
&& POC[report_param] = "Gross Profit"
),
VALUES ( POC[Country] ),
VALUES ( POC[Snapshot Date] ),
VALUES ( POC[Region] ),
--VALUES ( POC[Header_1] ),
VALUES ( POC[Header_2] ),
VALUES ( POC[project_number] )

)

FILTER ( ALL ( POC ), POC[Header_1] = "Upd.OEC" && POC[report_param] = "Sales" ),
VALUES ( POC[Country] ),
VALUES ( POC[Snapshot Date] ),
--VALUES ( POC[Header_1] ),
VALUES ( POC[Header_2] ),
VALUES ( POC[project_number] )
)

in my formula.

Result:

Best Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

v-lili6-msft · ‎12-10-2018