cancel
Showing results for
Did you mean:
Bhaveshp 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[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[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] )
)
)
- (
CALCULATE (
[Value Measure],
FILTER (
ALL ( POC ),
&& 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

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[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[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[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[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[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[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[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.
3 REPLIES 3
Bhaveshp 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[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[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[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[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[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[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[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.
Bhaveshp Member

Re: Need help with the calculated measure?

thank you @v-lili6-msft Announcements Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge. Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform. Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag. Top Kudoed Authors
Users Online
Currently online: 53 members 1,191 guests
Recent signins:
• epappu • sys_api_usr
• sure19 • augustindelaf • Amardeep100115 • DAKOFOED • AliceW • DAKOFOED • LucasMenezes • Marcusbja • BKLim • shaileshd2020 • pmurugesan • mabroukGadri 