Need help with the measure

[Bhaveshp]

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]))

Any help is greatly appreciated.

thanks

Bhavesh

[v-piga-msft]

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

[Bhaveshp]

@v-piga-msft I have created a measure (New_Value);

New_Value =

VAR Curr_Mo =

CALCULATE ( SUM ( POC[Value] ), (POC[Header_2]) = "Curr.mth." )

VAR Upd_OEC =

CALCULATE ( SUM ( POC[Value] ), (POC[Header_2]) = "Upd.OEC" )

RETURN

IF (

Min(POC[Header_2]) = "Curr.to OEC",

Curr_Mo - Upd_OEC,

SUM ( POC[Value] )

)

Look at the screenshot below, The Curr.to OEC is populating no values for my measure (New_Value).

Logic: If Header_2 = "Curr.to OEC" then value = (Curr.mth - Upd.OEC), else Value =  Value 

[Bhaveshp]

Can anyone help me with this  ?

[Anonymous · Accepted solution]

This was a good one 🙂  

Let's see how this shakes out:

1. Created a Values Measure because do not like using implied measures

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. 

 

[Bhaveshp]

Wow, Awesome, thank you @Anonymous for your solution . This is exactly what I'm looking for, It worked like a charm. 

[Bhaveshp]

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