Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I have a measure that is composed of other measures. Its form is
MeasureName = (Measure1+Measure2+Measure3)/3.
Where the three is, I want it to be dynamically determined. I want it to be the number of measures in the braces that are not equal to zero.
I've been stuck for a while. Does anyone know if this is possible?
Solved! Go to Solution.
Thanks for that info @DA12345 🙂
It seems your version of Excel / Power Pivot predates the table constructor syntax (with the braces).
And yes, [Value] is the column name in a single-column table created that way.
The idea with the measure is to create a table MeasureList, which has a single column containing the measure values. We then iterate over this table (with AVERAGEX) to produce the result.
We can use UNION/ROW functions (which should be available in your version of Power Pivot) to achieve the same result. I'm assuming VAR/RETURN is still valid in your version.
Does this work?
MeasureName =
VAR MeasureList =
UNION (
ROW ( "Value", [Measure1] ),
ROW ( "Value", [Measure2] ),
ROW ( "Value", [Measure3] )
)
VAR Result =
AVERAGEX (
MeasureList,
-- Convert any zeros to blank so that they are effectively ignored
IF ( [Value] <> 0, [Value] )
)
RETURN
Result
Regards,
Owen
Hi @DA12345
You could use the AVERAGEX function here, and take advantage of the fact that it ignores blanks.
Something like this:
MeasureName =
VAR MeasureList =
{
[Measure1],
[Measure2],
[Measure3]
}
VAR Result =
AVERAGEX (
MeasureList,
-- Convert any zeros to blank so that they are effectively ignored
IF ( [Value] <> 0, [Value] )
)
RETURN
Result
This measure will effectively sum the measures in MeasureList and divide by the number of nonzero measures.
Additional measures could be added to MeasureList if required.
Does this work for you?
Regards,
Owen
This measure notation does not seem to be accepted in PowerPivot where I would deploy the final solution.
Hi again,
It should work in current versions of Excel Power Pivot. It's working for me at least.
Just checking have you entered the measure similar to this screenshot?
There are some erroneous underlines, but the measure is still accepted.
If you are working with an earlier version of Power Pivot, please post back, and we can come up with some alternative syntax.
Regards,
Owen
This is what I tried and still got an error. I'm confused as to what [Value] is. I was assuming it was a reference to a value in the measurelist?:
MV3:=VAR MeasureList ={[NetCases1WeekAgo],[NetCases2WeekAgo],[NetCases3WeekAgo],[NetCases4WeekAgo],[NetCases5WeekAgo],[NetCases6WeekAgo]}
VAR Result = AVERAGEX(MeasureList, IF([Value]<>0,[Value]))
return Result
The error I got when repeating from your screenshot is: Operator or expression '{ }' is not supported in this context.
Thanks for that info @DA12345 🙂
It seems your version of Excel / Power Pivot predates the table constructor syntax (with the braces).
And yes, [Value] is the column name in a single-column table created that way.
The idea with the measure is to create a table MeasureList, which has a single column containing the measure values. We then iterate over this table (with AVERAGEX) to produce the result.
We can use UNION/ROW functions (which should be available in your version of Power Pivot) to achieve the same result. I'm assuming VAR/RETURN is still valid in your version.
Does this work?
MeasureName =
VAR MeasureList =
UNION (
ROW ( "Value", [Measure1] ),
ROW ( "Value", [Measure2] ),
ROW ( "Value", [Measure3] )
)
VAR Result =
AVERAGEX (
MeasureList,
-- Convert any zeros to blank so that they are effectively ignored
IF ( [Value] <> 0, [Value] )
)
RETURN
Result
Regards,
Owen
@DA12345 not sure what you are trying to achieve, if you are giving 3 measures in another measure, how do you want this measure list to grow dynamically?
I think it will be easier if you paste sample data with the expected output. Read this post to get your answer quickly.
Learn about conditional formatting at Microsoft Reactor
My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡
https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
User | Count |
---|---|
98 | |
90 | |
77 | |
71 | |
64 |
User | Count |
---|---|
114 | |
98 | |
96 | |
68 | |
67 |