CODEWITHSUNDEEP
powerbidaxpowerbi-desktop
kandarp
kandarp

Reputation: 343

AverageX of SubCategory in PowerBI DAX

I have Matrix visual in which I am showing Category and Sub-Category on row level.

I am trying to show AverageX at grand total row and at row total.

I am able to show correct value at grand total level but I am facing issue showing correct value of AverageX at row total level.

Consider below screenshot for example :

enter image description here

In the above example highlighted values are not correct.

In the Total field of ABC category, I would like to show 3.42 as a result by doing (4 + 2.84)/2 = 3.42 instead of 2.97.

I have tried quite a few ways to achieve this in DAX.

IF(ISFILTERED(Table),AVERAGEX(VALUES(Table[Category]),[Result]),[Result])

I also tried by using SUMMARIZE but it is not wokring either at row level :

 VAR SummaryTable =
     SUMMARIZE(
         'Table',
         'Table'[Category],
         'Table'[SUBCategory]
     )

 var subCateogryTotal =    AVERAGEX(
         SummaryTable,
         [Result]
     )

Upvotes: 0

Views: 86

Answers (2)

kandarp
kandarp

Reputation: 343

In case any one needed, here is the solution. I have added inline comment to explain the code. Result is measure which derives by doing ColumnA/ColumnB.

var totCategory = DISTINCTCOUNT(Table[Category])//get total count of category

RETURN if(totCategory = 1,
    AVERAGEX(VALUES(Table[SubCategory]),[Result]), //to show result on sub-category level
    AVERAGEX(VALUES(Table[Category]),AVERAGEX(VALUES(Table[SubCategory]),[Result]))) // calculate average of average for grand total row

Upvotes: 0

mxix
mxix

Reputation: 3659

Could you try this way:

Measure = 
AVERAGEX(
    VALUES('Table'[SubCategory]),
    [Result]
)

Upvotes: 1

Related Questions