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;
Rows = Report Parameters
Columns = Header_1, Header_2
Values= Value
I am trying to calculate the values based on the following logic;
If Header_2 = "Curr.to OEC" then value = (Curr.mth - Upd.OEC), else Value = Value
I have created the following measure but it is not working, what I am doing wrong here
New_Value = if(SELECTEDVALUE(POC[report_param] = "Curr.to OEC"), (CALCULATE(sum(Summary[Value Num]), FILTER(Summary, Summary[Header_2] = "Curr.mth.")) - CALCULATE(sum(Summary[Value Num]), FILTER(Summary, Summary[Header_2] = "Upd.OEC"))), Sum(Summary[Value Num]))
Solved! Go to Solution.
This was a good one 🙂
Let's see how this shakes out:
Value Measure = SUM ( POC[Value] )
Here's the code that will reference that measure:
Curr to OEC MATH =
if( VALUES(POC[Header_2]) = "Curr.to OEC",
CALCULATE(
[Value Measure],
FILTER(
ALL( POC),
POC[Header_2]="Curr.mth."),
VALUES( POC[report_param]),
VALUES( POC[project_number])
)
-CALCULATE(
[Value Measure],
FILTER(
ALL( POC),
POC[Header_2]="Upd.OEC"),
VALUES( POC[report_param]),
VALUES( POC[project_number])
),
[Value Measure]
)
Using Values as a fitler for calculate restores the initial filter context, so that's what we want.
Hi @Bhaveshp,
Based on your information, it seems that you have two tables.
If it is convenient, could you share your data sample which could reproduce your scenario and your dessired outpu so that we could help further on it?
Best Regards,
Cherry
@v-piga-msft I have created a measure (New_Value);
New_Value =
Can anyone help me with this ?
This was a good one 🙂
Let's see how this shakes out:
Value Measure = SUM ( POC[Value] )
Here's the code that will reference that measure:
Curr to OEC MATH =
if( VALUES(POC[Header_2]) = "Curr.to OEC",
CALCULATE(
[Value Measure],
FILTER(
ALL( POC),
POC[Header_2]="Curr.mth."),
VALUES( POC[report_param]),
VALUES( POC[project_number])
)
-CALCULATE(
[Value Measure],
FILTER(
ALL( POC),
POC[Header_2]="Upd.OEC"),
VALUES( POC[report_param]),
VALUES( POC[project_number])
),
[Value Measure]
)
Using Values as a fitler for calculate restores the initial filter context, so that's what we want.
Wow, Awesome, thank you @Anonymous for your solution . This is exactly what I'm looking for, It worked like a charm.
Hi @v-piga-msft, I have only one table which I'm trying to create a measure for the Value. Please reference the Sample pbix file in the link below.
https://drive.google.com/file/d/1cPbZvuu7R_IdfVqoW9xR6SSIT2EmZW42/view?usp=sharing
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 |
---|---|
112 | |
100 | |
76 | |
74 | |
49 |
User | Count |
---|---|
146 | |
108 | |
106 | |
90 | |
62 |