Reply
Highlighted
Member
Posts: 53
Registered: ‎02-19-2018
Accepted Solution

Need help with the measure

[ Edited ]

Hello, 

 

I have a table as shown below; 

Capture.PNG

 

 

 

 

 

 

 

 

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

 

 


Accepted Solutions
Established Member
Posts: 135
Registered: ‎10-26-2018

Re: Need help with the measure

This was a good one Smiley Happy  

 

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. 

Output.png 

View solution in original post


All Replies
Community Support Team
Posts: 2,674
Registered: ‎02-06-2018

Re: Need help with the measure

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

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Member
Posts: 53
Registered: ‎02-19-2018

Re: Need help with the measure

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

 

 

Member
Posts: 53
Registered: ‎02-19-2018

Re: Need help with the measure

[ Edited ]

@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).
 
Capture.PNG
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
Logic: If Header_2 = "Curr.to OEC" then value = (Curr.mth - Upd.OEC), else Value =  Value 
 
 
Member
Posts: 53
Registered: ‎02-19-2018

Re: Need help with the measure

Can anyone help me with this Man Sad ?

 

Established Member
Posts: 135
Registered: ‎10-26-2018

Re: Need help with the measure

This was a good one Smiley Happy  

 

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. 

Output.png 

Member
Posts: 53
Registered: ‎02-19-2018

Re: Need help with the measure

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