topic Re: Average a total in DAX Commands and Tips

Average a total

jonnyA — Mon, 12 Oct 2020 15:16:29 GMT

Hello,

Hoping sopmeone can help me average a total.

I am trying to average the "AvgDailyChg" row. I would greatly appreciate a solution to this problem!

I dont know if this will help, but I was given this formula from Icey, but I do not know how to plug in my Fields or measures with the formula that I was given. And I am not even suire if this forula will work, fyi.

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 I am working with.

Re: Average a total

manikumar34 — Tue, 13 Oct 2020 13:06:33 GMT

jonnyA ,

Not clear,

if you help with more detail and a sample of it. I wiil try to help you out.

Regards,

Manikumar

Re: Average a total

jonnyA — Tue, 13 Oct 2020 13:30:06 GMT

Thank you in advance for your help!

I think I am getting closer to my answer using the formula below. I am trying to average the Total. As you can see from the screen shot the Total is summing the 3 months.

Here is the formula I am currently using ...

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] )

)

Here is the formula I was provided from Icey via the Power Bi Community...

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] )
)

If you do provide an answer it would be extremely helpful to me if you could also provide the Fields to fill in for the formula.

For example, which Field I should be using for "Value", "Product", "B" and "C", etc.

Thank you so much 🙂