Count Result of a measure

[DA12345]

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?

[OwenAuger]

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

[DA12345]

This measure notation does not seem to be accepted in PowerPivot where I would deploy the final solution.

[OwenAuger]

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

[DA12345]

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

[DA12345]

The error I got when repeating from your screenshot is: Operator or expression '{ }' is not supported in this context.

[OwenAuger · Accepted 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

[parry2k]

@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.

✨ Follow us on LinkedIn and  to our YouTube channel

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