cancel
Showing results for
Did you mean:
Member

## Need help with the calculated measure?

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[project_number] )
)
/ (
CALCULATE (
[Value Measure],
FILTER ( ALL ( POC ), POC[report_param] = "Sales" ),
VALUES ( POC[Country] ),
VALUES ( POC[Snapshot Date] ),
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[report_param] = "Gross Profit"
),
VALUES ( POC[Country] ),
VALUES ( POC[Snapshot Date] ),
VALUES ( POC[project_number] )
)
/ CALCULATE (
[Value Measure],
FILTER (
ALL ( POC ),
&& POC[report_param] = "Sales"
),
VALUES ( POC[Country] ),
VALUES ( POC[Snapshot Date] ),
VALUES ( POC[project_number] )
)
)
- (
CALCULATE (
[Value Measure],
FILTER (
ALL ( POC ),
&& POC[report_param] = "Gross Profit"
),
VALUES ( POC[Country] ),
VALUES ( POC[Snapshot Date] ),
VALUES ( POC[Region] ),
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[project_number] )
)
)
),
[Value Measure]
)
)```

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

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support Team

## 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[project_number] )
)
/ (
CALCULATE (
[Value Measure],
FILTER ( ALL ( POC ), POC[report_param] = "Sales" ),
VALUES ( POC[Country] ),
VALUES ( POC[Snapshot Date] ),
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[report_param] = "Gross Profit"
),
VALUES ( POC[Country] ),
VALUES ( POC[Snapshot Date] ),
VALUES ( POC[project_number] )
)
/ CALCULATE (
[Value Measure],
FILTER (
ALLEXCEPT(POC,POC[project_number]),
&& POC[report_param] = "Sales"
),
VALUES ( POC[Country] ),
VALUES ( POC[Snapshot Date] ),
VALUES ( POC[project_number] )
)
)
- (
CALCULATE (
[Value Measure],
FILTER (
ALLEXCEPT(POC,POC[project_number]),
&& POC[report_param] = "Gross Profit"
),
VALUES ( POC[Country] ),
VALUES ( POC[Snapshot Date] ),
VALUES ( POC[Region] ),
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[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[report_param] = "Gross Profit"
),
VALUES ( POC[Country] ),
VALUES ( POC[Snapshot Date] ),
VALUES ( POC[project_number] )
)

FILTER (
ALL ( POC ),
&& POC[report_param] = "Sales"
),
VALUES ( POC[Country] ),
VALUES ( POC[Snapshot Date] ),
VALUES ( POC[project_number] )
)

FILTER (
ALL ( POC ),
&& POC[report_param] = "Gross Profit"
),
VALUES ( POC[Country] ),
VALUES ( POC[Snapshot Date] ),
VALUES ( POC[Region] ),
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[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.
3 REPLIES 3
Member

## Re: Need help with the calculated measure?

Highlighted
Community Support Team

## 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[project_number] )
)
/ (
CALCULATE (
[Value Measure],
FILTER ( ALL ( POC ), POC[report_param] = "Sales" ),
VALUES ( POC[Country] ),
VALUES ( POC[Snapshot Date] ),
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[report_param] = "Gross Profit"
),
VALUES ( POC[Country] ),
VALUES ( POC[Snapshot Date] ),
VALUES ( POC[project_number] )
)
/ CALCULATE (
[Value Measure],
FILTER (
ALLEXCEPT(POC,POC[project_number]),
&& POC[report_param] = "Sales"
),
VALUES ( POC[Country] ),
VALUES ( POC[Snapshot Date] ),
VALUES ( POC[project_number] )
)
)
- (
CALCULATE (
[Value Measure],
FILTER (
ALLEXCEPT(POC,POC[project_number]),
&& POC[report_param] = "Gross Profit"
),
VALUES ( POC[Country] ),
VALUES ( POC[Snapshot Date] ),
VALUES ( POC[Region] ),
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[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[report_param] = "Gross Profit"
),
VALUES ( POC[Country] ),
VALUES ( POC[Snapshot Date] ),
VALUES ( POC[project_number] )
)

FILTER (
ALL ( POC ),
&& POC[report_param] = "Sales"
),
VALUES ( POC[Country] ),
VALUES ( POC[Snapshot Date] ),
VALUES ( POC[project_number] )
)

FILTER (
ALL ( POC ),
&& POC[report_param] = "Gross Profit"
),
VALUES ( POC[Country] ),
VALUES ( POC[Snapshot Date] ),
VALUES ( POC[Region] ),
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[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.
Member

## Re: Need help with the calculated measure?

thank you @v-lili6-msft