cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
celenius
Helper I
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

DateSeriesCategoryValue
01/01/2020AA150
01/01/2020AA275
01/01/2020AA3100
01/02/2020AA160
01/02/2020AA2120
01/02/2020AA3100

 

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

Series01/01/202001/02/2020
 A225280
       15060
       275120
       3100100

 

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

SeriesJan 2020
A247,50
  155
  297,5
  3100

 

Any ideas? 

 

Chris

 

1 ACCEPTED SOLUTION
mahoneypat
Microsoft
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!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

4 REPLIES 4
mahoneypat
Microsoft
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!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


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.       

 

celenius_0-1595941861701.png

celenius_1-1595942291512.png

 

 

harshnathani
Super User
Super User

Hi @celenius ,

 

 

Try these measures

 

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

 

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

 

 

1.jpg

 

 

Regards,

Harsh Nathani

 

Hi @harshnathani 

 

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.

celenius_0-1595928415371.png

Chris

 

 

Helpful resources

Announcements
June 2022 update 768X460.jpg

Check it out!

Click here to read more about the June 2022 updates!

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power BI Dev Camp Session 23 768x460.jpg

Check it Out!

Mark your calendars and join us on Thursday, June 30 at 11a PDT for a great session with Ted Pattison!

June 20 episode 7 with aka link 768x460.jpg

The Power BI Community Show

Join us on June 20 at 11 am PDT when Kim Manis shares the latest on Azure Synapse analytics, the Microsoft Intelligent Data Platform, and notable Power BI Updates from Microsoft Build 2022.