Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello everyone!
I'm struggling with this calculation.
My goal is to get the sum off the value, but it has a twist:
1) I want to subtract the 1s step to the next one with a value, but in the same project.
For the project 17651, i want to subtract the 1st step in the 3rd(because it is the next with a value, 2nd step is blank)
For the project 45521, it should subtract step 1 to step 2.
Project |Step | | Value | Goal
|
Can you help me, please?
Solved! Go to Solution.
Hi,
I am not sure if I understood your question correctly, but please check the below picture and the attached pbix file.
Value measure: =
SUM( Data[Value] )
Goal measure: =
VAR _currentproject =
MAX ( Data[Project] )
VAR _minstepaftersteptwo =
MINX (
FILTER (
ALL ( Data ),
Data[Project] = _currentproject
&& Data[Step] > 1
&& Data[Value] <> BLANK ()
),
Data[Step]
)
VAR _steponevalue =
CALCULATE ( [Value measure:], Data[Step] = 1 )
RETURN
IF (
HASONEVALUE ( Data[Project] ),
SWITCH (
TRUE (),
[Value measure:] = BLANK (), 0,
MAX ( Data[Step] ) = 1, [Value measure:],
MAX ( Data[Step] ) = _minstepaftersteptwo, [Value measure:] - _steponevalue,
[Value measure:]
)
)
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Hi,
I am not sure if I understood your question correctly, but please check the below picture and the attached pbix file.
Value measure: =
SUM( Data[Value] )
Goal measure: =
VAR _currentproject =
MAX ( Data[Project] )
VAR _minstepaftersteptwo =
MINX (
FILTER (
ALL ( Data ),
Data[Project] = _currentproject
&& Data[Step] > 1
&& Data[Value] <> BLANK ()
),
Data[Step]
)
VAR _steponevalue =
CALCULATE ( [Value measure:], Data[Step] = 1 )
RETURN
IF (
HASONEVALUE ( Data[Project] ),
SWITCH (
TRUE (),
[Value measure:] = BLANK (), 0,
MAX ( Data[Step] ) = 1, [Value measure:],
MAX ( Data[Step] ) = _minstepaftersteptwo, [Value measure:] - _steponevalue,
[Value measure:]
)
)
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.