Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Bhaveshp
Helper III
Helper III

Need help with the measure

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

 

 

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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. 

Output.png 

View solution in original post

6 REPLIES 6
v-piga-msft
Resident Rockstar
Resident Rockstar

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.

@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 
 
 

Can anyone help me with this Man Sad ?

 

Anonymous
Not applicable

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. 

Output.png 

Wow, Awesome, thank you @Anonymous for your solution Smiley Very Happy. 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

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.