Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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[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.
Solved! Go to Solution.
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:
Best Regards,
Lin
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:
Best Regards,
Lin
Here is the link for pbix for reference
https://drive.google.com/file/d/1ZZPJqpztSbHu4u1BroLAcjN1rRWLTQzB/view?usp=sharing
thanks
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |