Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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
Employee
Employee

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
Employee
Employee

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


Anonymous
Not applicable

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
Community Champion
Community Champion

Hi @Anonymous ,

 

 

Try these measures

 

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

 

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

 

 

1.jpg

 

 

Regards,

Harsh Nathani

 

Anonymous
Not applicable

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors