Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
Bhaveshp
Helper III
Helper III

Need help with the calculated measure?

‎12-07-2018 11:34 AM

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

 

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

 

appreciate your help.

 

Message 1 of 4
604 Views
0
1 ACCEPTED SOLUTION
v-lili6-msft
Community Support
Community Support

‎12-10-2018 01:38 AM

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

Message 3 of 4
572 Views
0
3 REPLIES 3
v-lili6-msft
Community Support
Community Support

‎12-10-2018 01:38 AM

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.
Message 3 of 4
573 Views
0
Bhaveshp
Helper III
Helper III
In response to v-lili6-msft

‎12-11-2018 10:17 AM

thank you @v-lili6-msft Man Happy 

Message 4 of 4
566 Views
0
Bhaveshp
Helper III
Helper III

‎12-07-2018 11:37 AM

Here is the link for pbix for reference

 

https://drive.google.com/file/d/1ZZPJqpztSbHu4u1BroLAcjN1rRWLTQzB/view?usp=sharing

 

thanks

Message 2 of 4
603 Views
0

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel1

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

View All