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: 

Get inspired! Check out the entries from the Power BI DataViz World Championships preliminary rounds and give kudos to your favorites. View the vizzies.

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
832 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
800 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
801 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
794 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
831 Views
0

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code FABINSIDER for a $400 discount!

FebPBI_Carousel

Power BI Monthly Update - February 2025

Check out the February 2025 Power BI update to learn about new features.

March2025 Carousel

Fabric Community Update - March 2025

Find out what's new and trending in the Fabric community.

View All
Top Solution Authors
View All