in a power BI matrix can you sum some totals and average others?

[jonnyA]

I created a matrix and the Totals in summing all the rows, but I need the highlighted totals to show an average.  Any idea's on how I can make this work?  Example: I want 248 to be 124, which is the average of 127 and 121.

[Icey · Accepted solution]

Hi @jonnyA ,

A simple example:

Measure =
IF (
    NOT ( HASONEVALUE ( 'Table'[YearMonth] ) )
        && SELECTEDVALUE ( 'Table'[Product] ) IN { "B", "C" },
    AVERAGE ( 'Table'[Value] ),
    SUM ( 'Table'[Value] )
)

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[jonnyA]

Hey @Icey Icey, Thanks for your answer!!!

But I need help plugging in the formula you provided, i'm a beginner level Power BI analyst.

You provider this formula ...

Measure =
IF (
    NOT ( HASONEVALUE ( 'Table'[YearMonth] ) )
        && SELECTEDVALUE ( 'Table'[Product] ) IN { "B", "C" },
    AVERAGE ( 'Table'[Value] ),
    SUM ( 'Table'[Value] )
)

Here are the Fields I have for the dataset mentioned ...See pic for the "Fields" I have to work with.

[jonnyA]

@Icey Did you see my response?  I am stuck with trying to get this to work.

Thank you in advance!

[Icey]

Hi @jonnyA ,

Sorry to reply late. Please try this:

Measure =
IF (
     (
        NOT ( HASONEVALUE ( 'Table'[YearMonth] ) )
            && SELECTEDVALUE ( 'Table'[Product] ) IN { "B", "C" }
    )
        || (
            NOT ( HASONEVALUE ( 'Table'[YearMonth] ) )
                && NOT ( HASONEVALUE ( 'Table'[Product] ) )
        ),
    AVERAGE ( 'Table'[Value] ),
    SUM ( 'Table'[Value] )
)

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[jonnyA]

@Icey @Greg_Deckler 

I think I am close to getting this formula to work.

Here is the formula that Icey gave me, but I did tweak it some (Probaly easier to read in the screenshot I provided)...

Power BI Comm 2 =

IF (

(

NOT ( HASONEVALUE ( 'DateOfServiceAnalysis'[MTHYear_Sort] ) )

&& SELECTEDVALUE ( 'DateOfServiceAnalysis'[MTHYear_Sort] ) IN { "AvgDailyChg" }

)

|| (

NOT ( HASONEVALUE ( 'DateOfServiceAnalysis'[MTHYear_Sort] ) )

&& NOT ( HASONEVALUE ( 'DateOfServiceAnalysis'[AvgDailyChg] ) )

),

AVERAGE ( 'DateOfServiceAnalysis'[AvgDailyChg] ),

SUM ( 'DateOfServiceAnalysis'[AvgDailyChg] )

)

Do you two have any advice on how I can make this work for the total to Average?

Thanks so much in advance!

[Icey]

Hi @jonnyA ,

Are these the names of multiple measures? The value field is not just one measure?

Best Regards,

Icey

[jonnyA]

@Icey It is just one measure.

[Greg_Deckler]

@jonnyA - If you can identify those rows somehow like HASONEVALUE or ISINSCOPE, probably, see MM3TR&R - https://community.powerbi.com/t5/Quick-Measures-Gallery/Matrix-Measure-Total-Triple-Threat-Rock-amp-...

Would need sample data to be more specific. Or a more encompassing screen shot.