Reply
Highlighted
Member
Posts: 53
Registered: ‎02-19-2018
Accepted Solution

Need help with the calculated measure?

[ Edited ]

Hello, 

 

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

Capture.PNG

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]
    )
)

 

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

 

appreciate your help.

 


Accepted Solutions
Community Support Team
Posts: 1,161
Registered: ‎07-30-2018

Re: Need help with the calculated measure?

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:

19.JPG

 

 

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


All Replies
Member
Posts: 53
Registered: ‎02-19-2018

Re: Need help with the calculated measure?

Community Support Team
Posts: 1,161
Registered: ‎07-30-2018

Re: Need help with the calculated measure?

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:

19.JPG

 

 

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.
Member
Posts: 53
Registered: ‎02-19-2018

Re: Need help with the calculated measure?

thank you @v-lili6-msft Man Happy