Sum of Average data that Group by 2 Columns

[PowerBi888]

Hi,

            I would like to group data from 2 Colum from TableSale and average Target Sale Value, then sum in total value :

Branch ,      Product  , ColumnC , Target Sale Value

Branch1   ,  Product1 ,  Day1       , 10

Branch1   ,  Product1 ,  Day2       , 10

Branch1   ,  Product2 ,  Day1       , 5

Branch1   ,  Product2 ,  Day2       , 5

Branch1   , Product3 ,   Day1       ,  3

Branch1   , Product3 ,   Day1       ,  3

               I would like to show Matrix result like this :

====================================

Branch ,      Product , Average Target Sale Value

Branch1   ,  Product1 , 10

Branch1   ,  Product2 ,   5

Branch1   ,  Product3 ,   3

Total                   =     ,  18

              now I use this measure -->

AverageValueX = SUMX( TableSale ,CALCULATE(AVERAGEX(TableSale,Target Sale Value),ALLEXCEPT( TableSale ,Branch ,      Product)))

   but Data it's not average

[VahidDM · Accepted solution]

Hi @PowerBi888 

Try this measure:

Avr. =
VAR _A =
    AVERAGE ( 'Table'[Target Sale Value] )
RETURN
    IF (
        HASONEVALUE ( 'Table'[Product] ),
        _A,
        SUMX (
            SUMMARIZE (
                'Table',
                'Table'[Branch ],
                'Table'[Product],
                "AV", AVERAGE ( 'Table'[Target Sale Value] )
            ),
            [AV]
        )
    )

 output:

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/

[PowerBi888]

Thank you VahidDM , this solution I solved it. 

[jaideepnema]

Hi @PowerBi888 ,
Create a measure like this

Average = SUMX(VALUES('Table'[Product]),calculate(AVERAGE('Table'[Target Sales Value]),ALLEXCEPT('Table','Table'[Product])))

Check the attached file for your reference.

Please accept this as a solution if your question has been answered !!

Appreciate a Kudos 😀

Connect with me on LinkedIn: https://www.linkedin.com/in/jaideepnema/

[PowerBi888]

Hi , Average value show correct , but my issued it's grand total in Matrix show not correct. It's not sum vlaue