cancel
Showing results for
Did you mean:
Helper I

## DAX Sum of Averages and Time Dimension

Hi,

Got this problem I can't figure out how to solve, below is an simplified example of it.

I need to get the numbers from Table 1 into a matrix visualisation. The matrix will be grouped by series and category on row level and by date hierarchy on columns.The subtotals for the series should be the SUM whereas the category should be AVERAGE for any given timeperiod.

Table 1

 Date Series Category Value 01/01/2020 A A1 50 01/01/2020 A A2 75 01/01/2020 A A3 100 01/02/2020 A A1 60 01/02/2020 A A2 120 01/02/2020 A A3 100

This should be the result when doing drill-down to Date.

 Series 01/01/2020 01/02/2020 A 225 280 1 50 60 2 75 120 3 100 100

And this should be the result when drill-up from Date to Month

 Series Jan 2020 A 247,50 1 55 2 97,5 3 100

Any ideas?

Chris

1 ACCEPTED SOLUTION
Microsoft

Please try this expression, replacing Data with your actual table name.  I didn't know if you have a Date table or not, so I just used the Date column in the table, and made  Month column in that table to test it out.

Total =
AVERAGEX ( VALUES ( 'Data'[Date] ), CALCULATE ( SUM ( 'Data'[Value] ) ) )

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

4 REPLIES 4
Microsoft

Please try this expression, replacing Data with your actual table name.  I didn't know if you have a Date table or not, so I just used the Date column in the table, and made  Month column in that table to test it out.

Total =
AVERAGEX ( VALUES ( 'Data'[Date] ), CALCULATE ( SUM ( 'Data'[Value] ) ) )

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

Helper I

Hi @mahoneypat ,

and thanks for your suggested solution. This expression works exactly the way I want it in my simplified example, but when implementing the same expression to the real data, the subtotals are wrong. Have to dig into this further to see what the problem is.

Super User

Hi @celenius ,

Try these measures

``AverageV = Average('Table'[Value])``
``SUMV = SUM('Table'[Value])``

``Measure = IF(ISINSCOPE('Table'[Category]),[AverageV],[SUMV])``

Regards,

Harsh Nathani

Helper I

Thanks for your suggestion. There is however a problem with the month total. I would need this to be 55+97,50+100 = 202,50, i.e it should add up the values from the categories rows and not the series row.

Chris

Announcements