Forum Discussion

depple's avatar
depple
Icon for Helper III rankHelper III
7 years ago
Solved

Single value from group - Correct sum in visual matrix

Hi,

 

I have a data table like this:

Data  
Product GroupProductValue
StarStar Alfa7
StarStar Bravo7
StarStar Charlie7
SunSun Mike12
SunSun November12
SunSun Oscar12
SunSun Papa12

 

I use this measure:

 

Measure = CALCULATE(AVERAGE('Data'[Value]);FILTER('Data';'Data'[Sales]=0))

 

In this matrix:

 

   
SalesmanStarSun
Salesman A712
   Store 1712
   Store 2712

 

My problem is that though the values for each store is like I want it, I want the vertical sum for Salesman to be 14 and 24. I know that the reason for this is the use of the AVERAGE-function in the measure, but how should I build the measure to get the correct Sum for Salesman, like this:

 

   
SalesmanStarSunMeasure
Salesman A1424
   Store 1712
   Store 2712

 

Thanks in advance for any suggestions!

 

/depple

Need Help
  • v-yulgu-msft's avatar
    v-yulgu-msft
    7 years ago

    Hi depple ,

     

    Add below measure into Matrix.

    Average = SUMX(VALUES(Data_1[Store]),[Measure])

     

    Best regards,

    Yuliana Gu

1 Reply

  • v-yulgu-msft's avatar
    v-yulgu-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    7 years ago

    Hi depple ,

     

    Add below measure into Matrix.

    Average = SUMX(VALUES(Data_1[Store]),[Measure])

     

    Best regards,

    Yuliana Gu